Truncating Database Log Files

Discovered today that I needed to shrink some database files to retake some drive space on a SQL Server 2005 box. Shrinking the database and its files are relatively simple (right-click -> Tasks -> Shrink -> follow the wizard). However, what wasn’t so obvious, is that on this particular database the log file was around 10 GB (the database as a whole was only 15 GB). Since this is a dev environment the easiest way to shrink that log file is to just truncate it.

You can use the following command to truncate a log file in sql server:

  1. BACKUP LOG myDatabaseName WITH TRUNCATE_ONLY

Simple, easy and quick. However, be warned that this particular syntax is being depricated in newer versions of SQL Server… see this article for more information:

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/23/845.aspx