every once in a while i run into an issue where i have some log file on a microsoft sql server that has not been properly configured and is taking up a hundred gigs.

and inevitably, i end up spending the next 20 minutes to find a proper example of how to truncate the logs. so, instead of searching again, i am posting it on my site 😉

WARNING: don’t do this unless you have backups or you really, really don’t want to roll your database back. your deleting transaction logs, so while it won’t hurt your working database, it will prevent you from rolling back to yesterday. ye be warned.

in this case, i am running these commands on a microsoft sql server 2005 install, but i would presume it to work on sql 2008 or 2012, although i haven’t tested it.

here is the code:

-- specify database and show database & log statistics
use dbname
exec sp_helpfile

-- truncate the log
USE dbname
GO
BACKUP LOG dbname WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (dbname_log, 1)
GO
DBCC SHRINKFILE (dbname_log, 1)
GO

--show statistics after truncating
exec sp_helpfile

reference/disclaimer: this code is from http://www.sqlcleanup.com/2008/sql-2005-truncating-log-files-and-recovering-space/ and is not my work, i just can’t always find it in a pinch.