Shrink the Unshrinkable SQL Transaction Log

Shrink the Unshrinkable SQL Transaction Log

There are many reason for not shrinking of Transaction log. Our production database backup was failing for last several weeks, so the backup process could not clear out the transaction log. Our approx 1 GB database grow up to 37.5 GB.

The SQL Server GUI for shrinking the database rendered no effect, and even using the DBCC SHRINKFILE command was not working.

The key, as explained by Pinal Dave, is to run the SHRINKFILE command twice,with an explicit backup log truncation in between both runs. This code here will get you up and running:

DBCC SHRINKFILE("DemoData_Log", 1)

BACKUP LOG DemoData WITH TRUNCATE_ONLY

DBCC SHRINKFILE("DemoData_Log", 1)