Wednesday, February 20, 2008

Truncate SQL log on development machines

By default the recovery model in SQL Server is full, so if you have a development server that doesn't have scheduled backup, the database log file can potentially grow out of control.  You can either set the recovery model to "simple", and then truncate the file.  Or keep the recovery model as full and force truncation.  Obviously shouldn't do this blindly in production unless you understand the implications.  This gets asked frequent enough, but not enough to remember the syntax, so this post is here as a memory jogger.

USE dbname

BACKUP LOG dbname WITH TRUNCATE_ONLY

DBCC SHRINKFILE (logical_log_filename, 10)

No comments: