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
Post By
Bruusi
04:33
11 Apr 2006
» Next Post:
Validator Source available for download
« Previous Post:
Extending the Validator Module
Comments:
Posted by
Frango
@
19 Jul 2007
16:48
-- here is what I use in 2005, seems to work ok...
BACKUP LOG [database_name] WITH TRUNCATE_ONLY
DBCC SHRINKFILE (' [log filename stem] ')