Bart's Weblog

Just a blog…

SQL Server: Shrinking SQL transaction log

Posted by bartvdw on 1313/0303/2011

Had an issue recently regarding shrinking SQL transaction log of a system DB. Short information about below, full in the link at the bottom (Tibor Karaszi). Really helped me in understanding and solving my issue!

By default the model system DB is configured with recovery model Full. This makes that it has a transaction log that will grow in time… Below how to accomplish shrinking use SQL Query, which gives you best info:

  • Simple recovery model
    USE dbname
    CHECKPOINT
    –First param below is fileno for log file, often 2. Check with sys.database_files
    –Second is desired size, in MB.
    DBCC SHRINKFILE(2, 500)
    DBCC SQLPERF(LOGSPACE) –Optional
    DBCC LOGINFO –Optional
    Now repeat above commands as many times as needed!
  • Full or bulk_logged recovery model
    USE dbname
    BACKUP LOG dbname TO DISK = ‘C:\x\dbname.trn’
    –First param below is fileno for log file, often 2. Check with sys.database_files
    –Second is desired size, in MB.
    DBCC SHRINKFILE(2, 500)
    DBCC SQLPERF(LOGSPACE) –Optional
    DBCC LOGINFO –Optional
    Now repeat above commands as many times as needed!

 

Recovery Models for System Databases
http://msdn.microsoft.com/en-us/library/ms365937%28v=sql.90%29.aspx

Why you want to be restrictive with shrink of database files
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

System Databases in SQL Server
http://www.sql-server-performance.com/articles/dba/System_Databases_in_SQL_Server_p1.aspx

SQL Server 2005 Recovery Models
http://dbarecovery.com/backup-and-recovery-overview/sql-server-2005/sql-server-2005-recovery-models.html

Advertisements

One Response to “SQL Server: Shrinking SQL transaction log”

  1. hemp said

    This model is typically used by organizations running large bulk operations that degrade system performance and do not require point in-time recovery. By default the SQL Server system databases are configured with the simple recovery model.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: