Transaction Log Not Shrinking

We have run into cases where the SQL Server transaction log would not shrink. This could be caused by a few things.

Logging Mode


(Note: The exact steps might vary slightly based on the version of SQL you are running.)

  • Open SQL Management Studio
  • Right click on the database name, choose "Properties"
  • Choose Options
  • Check that "Recovery Model" is Simple.

If not, change it to Simple. This can be done to a running StoreData without risk.

Log File Initial Size


(Note: The exact steps might vary slightly based on the version of SQL you are running.)

  • Open SQL Management Studio
  • Right click on the database name, choose "Properties"
  • Choose Files
  • Check that Initial Size (MB) for the log file (second row usualy) is small, preferably 1.

If not, change it to 1. This can be done to a running StoreData without risk.

  • Click OK

Check the log file size. This should immediately reduce it if this was the problem.

If the log file size doesn't change, proceed to the next step.

Database in Replication Mode


If the database somehow gets put in Replication mode, it will not delete the log files until replication is complete. Since Control data is not replicated, this will never occur.

To check if it is replication mode, run this SQL:
SELECT  Name,Recovery_Model_Desc, Log_Reuse_Wait_Desc
FROM    Sys.DATABASES

Find the database and look at the Log_Reuse_Wait_Desc. If it says Replication, then you will need to delete the replication setting.

Backup the Database and run this command:

To check if it is replication mode, run this SQL (adjusting the database name if applicable):
EXEC sp_removedbreplication @dbname = 'STOREDATA' 
;
SELECT  Name,Recovery_Model_Desc, Log_Reuse_Wait_Desc
FROM    Sys.DATABASES

After the database is out of replication mode, reset the log file as indicated in the previous section.


See Also