A Primer on Log Shrinking and Truncation in SQL Server
Shrinking the Log
To shrink the physical log file, you can use one of the DBCC Maintenance Statements – DBCC SHRINKFILE. This statement can be run under either database recovery model. The following script will truncate the log by setting the recovery model to SIMPLE, shrink the log to 10 MB using DBCC SHRINKFILE, and then reset the recovery model to FULL:
USE AdventureWorks GO ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE GO DBCC SHRINKFILE (AdventureWorks_Log, 10) GO ALTER DATABASE AdventureWorks SET RECOVERY FULL GOAgain, be aware that the LSN chain is immediately broken when the recovery model is set to SIMPLE.
Restarting the Chain
To restart the LSN log chain, immediately perform a full or differential backup after resetting the recovery model to FULL, and then resume normal log backups:
BACKUP DATABASE AdventureWorks TO DISK = 'c:\backups\advw20120317_new.bak' GO BACKUP LOG AdventureWorks TO DISK = 'c:\backups\advw20120317_new.trn'A successful backup of the log will confirm the integrity of the LSN chain. It is essential to get the log chain started again as soon as possible to ensure point-in-time recovery.
Pages: 1 2
Nice and small Primer, Seth!
Still, there could be an addition: Why not use TRUNCATEONLY in the DBCC command? It truncates just back to the point of the last log backup (or the given size, if that’s reached before) and does not need a switch to SIMPLE recovery, thus not breaking the LSN chain:
USE AdventureWorks
GO
DBCC SHRINKFILE (AdventureWorks_Log, 10, TRUNCATEONLY)
Important is to run this command after a log backup and best after a checkpoint. Like this you are sure that the log contains as less virtual log files as possible. But be aware that if you truncate the log too much, it will have to grow right after this operation. And logfile growth in FULL recovery needs time and therefore slows down transactions on the database
Thanks for the comment, Christian. The DBCC SHRINKFILE TRUNCATEONLY option targets the data file only, not the log file. It does not apply if you are using DBCC SHRINKFILE on a transaction log file. The MSDN Library says:
TRUNCATEONLY
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.
TRUNCATEONLY is applicable only to data files.
http://msdn.microsoft.com/en-us/library/ms189493.aspx
I use the UI to perform a shrink on the transaction file, then backup the transaction file, then perform the shrink again. There is no need to change your recovery model and thus break the LSN chain. I don’t remember the specifics, but the shrink operation needs to be run twice because the first time it was run, if the new entries are being written to the log at the end of the file, not all unused space can be physically removed. However, if the shrink operation detects this, it will fill up the rest of the log file with null space (or reset the pointer). Then when a transaction backup happens, the virtual space at the end of the log file is removed. Durning the second shrink, the log file will physically shrink to its smallest size. Then you can let it grow as needed, but hopefully max out at a smaller size.