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 GO
Again, 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.
]]>