Monday, September 14, 2009

Truncate database logs

If you create a SQL Server database, by default it's configured to use the 'Full' recovery model. As a consequence your transaction logs are only cleaned up after a full backup. So if you're database is not backed up(hopefully it is), your log files keep growing and growing.
You can always clean up the log files yourself. First check for any open transactions by using following command:
DBCC OPENTRAN(<TransactionLogName>)
If there are no running transactions, you can safely run following commands:
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

This will shrink the logs to the minimum size possible.

No comments: