Thursday, April 15, 2010

Truncate SQL Server Transaction Log

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Transaction logs are truncated on backup or checkpoints depending on the model you're using:

http://technet.microsoft.com/en-us/library/ms189085.aspx

However the backup will include the larger transaction log so you'll want to back up after truncation to get a smaller backup file and not have to restore the entire transaction log.