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
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.

Pages: 1 2




Array

No comments yet... Be the first to leave a reply!