Skip Navigation LinksHome > View Post

Truncating the transaction log

Our continuous integration failed today because of the following error: The transaction log for database 'TfsVersionControl' is full.

I can never remember how to truncate the transaction log, so I thought I would post about it for my own reference also.

I would suggest taking a backup of the database before truncating the log in the manner described in this article as any transactions after the last backup will be deleted.

Truncating the log

Open Enterprise Manager or SQL Server Management Studio and execute the following:


BACKUP LOG [database_name] WITH {TRUNCATE_ONLY | NO_LOG}

Note: The WITH TRUNCATE_ONLY and WITH NO_LOG options are synonyms in newer versions of SQL Server.
The BACKUP LOG statement frees up space within the log file, but does not actually reduce the size of the file. See the following article on MSDN if you also want to shrink the transaction log. I don't know if this article still applies to SQL 2005, so any feedback would be welcome :D

Tags: SQL

 
Bruusi Post By Bruusi
4:33 AM
11 Apr 2006

» Next Post: Validator Source available for download
« Previous Post: Extending the Validator Module

Comments are closed for this post.

Posted by Frango @ 19 Jul 2007 4:48 PM
-- here is what I use in 2005, seems to work ok...
BACKUP LOG [database_name] WITH TRUNCATE_ONLY
DBCC SHRINKFILE (' [log filename stem] ')

© 2005 - 2014 Josh Twist - All Rights Reserved.