Shrink a LogFile in SQL

Over time, log files on SQL databases can become bloated. This procedure will provide scripts to shrink a log file back to manageable sizes.

1) Perform a complete backup of the database

2) Perform a backup of the transaction log file

3) Run following script to shrink the log file
USE [DB_NAME]
DBCC SHRINKFILE (N'DB_Logical_Name' , 100, TRUNCATEONLY)
BACKUP LOG DB_NAME WITH TRUNCATE_ONLY

4) Check the size of the log file. If it has not shrunk, there may be some data causing it to fail the move. Use the following script to create a sample table and fill it with data which _should_ free up the stuck data.

/* Create the table */
USE [DB_NAME]
DROP TABLE MyTable
CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )
INSERT Mytable (PK) VALUES (1)

/* Fill the table */
SET NOCOUNT ON
DECLARE @Index INT
SELECT @Index = 0
WHILE (@Index < 20000)
BEGIN
   UPDATE MyTable SET MyField = MyField WHERE PK = 1
   SELECT @Index = @Index + 1
END
SET NOCOUNT OFF

5) After running the script rerun the shrink script. The transaction log should now shrink.

USE [DB_NAME]
DBCC SHRINKFILE (N'DB_Logical_Name' , 100, TRUNCATEONLY)
BACKUP LOG DB_NAME WITH TRUNCATE_ONLY

6) If it still hasn't shrunk, delete data from the MyTable table and rerun the fill script. Try the shrink again.

/* Diagnostics */
DBCC LOGINFO

 

This tip came from Rob Bamforth on his blog at http://robbamforth.wordpress.com/2009/11/16/sql-how-to-shrink-un-shrinkable-database-log-database-log-wont-shrink/