How to Shrink SQL Server Transaction Logs


Back up your database first.

Launch SQL Server Management Studio. Open up a query window associated with the database with the large transaction log. (Right-click on the DB and choose new query.)

Get the logical name of the transaction log file. (Right-click on the DB, select Properties, then in the Files screen, grab the Logical Name… probably ends in something like _Log.)

Execute the following, substituting with the appropriate logical name of the database log file, no quotes needed:

DBCC SHRINKFILE(<log_file_name_Log>)
BACKUP LOG <database> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<log_file_name_Log>)

A little more information :

The ‘proper’ thing to do these days is to put the database into ‘simple recovery’ and then to shrink the log.

A few commands that I think might do it :

ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE
DBCC SHRINKFILE(<log_file_name_Log>) 
ALTER DATABASE [mydatabase] SET RECOVERY FULL

Some notes on SQL Server backups :

  • transaction logs have to be backed up in order for them to ‘truncate’. If they are not backed up regularly, they will eventually fill the disk
  • a full backup alone will not do the trick – that does not touch the transaction log
  • transaction logs must be backed up via ‘BACKUP LOG’
  • if the dba does not require transaction log backups, it is advisable to move to Simple Recovery
Advertisements

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