SQL Server 2008 – Truncating Transaction Log

This article describes how to truncate transaction log file in SQL Server 2008 and 2005.

Truncating Transaction Log in SQL Server 2005

In SQL Server 2005 you need to run these commands:

dbcc shrinkfile(dbname_log,1)
backup log dbname WITH truncate_only
dbcc shrinkfile(dbname_log,1)

Here dbname_log is a logical file name of the transaction log file. You can see the list of files with their sizes by using this command:

SELECT * FROM sys.database_files

The command “Backup log .. with truncate_only” removes an inactive part of the log. The size of the transaction log file on disk remains the same after running this command.

Shrinkfile command releases the unused space to the OS.

Truncating Transaction Log in SQL Server 2008

You need to truncate the transaction log file when you want to free an additional space on a hard disk or truncate the log file after backups.

If you use SQL Server 2008 Express Edition then you have a 10GB limit for a database size including the transaction log files. So if you don’t want to go beyond the limit you should truncate log file.

If the transaction log file is full you will get this error:

The transaction log for database ‘_dbname_’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

 

The commands shown for SQL Server 2005 do not work. BACKUP LOG with the option ‘TRUNCATE_ONLY’ gives an error in SQL Server 2008.

‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

 

Backup log with truncate_only is no longer supported in SQL 2008.

The only way that works is to change the recovery model to SIMPLE, then shrink the file, and, finally, return the recovery model back to the original state (usually, FULL):

ALTER DATABASE dbname SET recovery simple
go
 
DBCC SHRINKFILE (N'dbname_log', 1)
go
 
checkpoint
go
 
ALTER DATABASE dbname SET recovery FULL
go

Check the result:

SELECT * from sys.database_files

 

Read more:

Read this article in TechNet about how How Log Truncation Works .

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>