Here is a typical scenario every SQL Server DBA faces: Transaction Log grows unexpectedly or becomes full!!
So, do I need to increase the Log size? Fortunately we have a better solution – SHRINK the log file!
Typically log truncation frees space in the log file for reuse by the Transaction Log thus releasing space and making it not to over grow in size. This truncation occurs automatically when:
- Database is backed up – under Simple recovery model
- Transaction log is backed up – under Full recover model
When the database is in simple recovery mode I usually don’t expect the T-log to be blowing out. This happened to me and I figured out this is because of increasing number of uncommitted transactions!
In this case I am running a purge job to purge data from a management data warehouse (MDW) database (about 1600+ GB) which is configured to receive data from multiple instances as part of the MDW Data Collection configuration setup. And below is what I did to overcome this problem.
First I checked the log size and log space used% for every database using the following DBCC command:
After getting to know the log size (around 900+ GB), I shrinked it using the below command to some 800+ MB
USE DatabaseName; GO --Truncate the log by changing the database recovery model to SIMPLE --(If it?s not already in SIMPLE recovery model) ALTER DATABASE DatabaseName SET RECOVERY SIMPLE; GO --Shrink the truncated log file to 1 MB --(However it won?t shrink a file past the size needed to store the data in the file) DBCC SHRINKFILE (DatabaseName_Log, 1); GO --Reset the database recovery model. ALTER DATABASE DatabaseName SET RECOVERY FULL; GO
Shrinking occurs only when at least one virtual log file (VLF) is free, which means in my case I have tons of inactive VLFs as I am able to shrink it to 800 MB from 900 GB! (amazing isn’t it?)
Please refer BOL for the options above. Also, this can be done using GUI by right clicking on database ->Tasks->Shrink->Files
Here is more information from MSDN BOL:
- Shrinking the Transaction Log
- Factors That Can Delay Log Truncation
- Troubleshoot a Full Transaction Log (SQL Server Error 9002)
- KB Article: A transaction log grows unexpectedly or becomes full in SQL Server