5/26/22, 12:00 PM

Knowledge Article

How to Truncate the Transactional Log in SQL Server 2008

Body

Topic:

On rare occasions it may be necessary to shrink or truncate the transaction log of an SQL Database if it becomes too large. Most SQL backup software will either automatically truncate the log file, or will offer options to do this automatically. In some cases, it may be necessary to truncate the logs manually. The transaction database is a record of all queries, inserts, deletes and updates that are done against the database. If needed an administrator can update a restored database from the transaction logs.

Product version affected:

All SES versions

Environment:

Windows Server OS and SQL 2008 Full and SQL Express

Steps to follow:

Note: The scripts in this article are only intended for Microsoft SQL Server 2008.

Warning: Do a complete backup before running the following commands.

1. It is recommended to perform a back up of the Transaction Log (SQL Server) before running the below queries
https://msdn.microsoft.com/en-CA/library/ms179478.aspx
Optional: Customer can consider doing a Full Database Backup (SQL Server)
https://msdn.microsoft.com/en-CA/library/ms187510.aspx

2. Temporarily change the database recovery model to "simple".

USE [master]
GO
ALTER DATABASE [SESDB]     Note: Replace <SESDB> with the name of the database.
SET recovery simple WITH no_wait
GO

2. Issue a checkpoint.

CHECKPOINT
GO

The checkpoint process writes all the dirty pages in the memory to disk. On a simple recovery mode, the checkpoint process clears the inactive portion of the transaction log.

3. Shrink the transaction log.

USE <SESDB>     Note: Replace <SESDB> with the name of the database.
GO
DBCC shrinkfile(2, 2, truncateonly)

https://winmagic.my.salesforce.com/articles/Service/How-to-Truncate-the-Transactional-Log-in-SQL-Server-2008/p

1/2

5/26/22, 12:00 PM

Knowledge Article

Shrinking the log file with a "truncateonly" option clears the unused space at the end of the log file. The first parameter of "shrinkfile" takes the file id within the database. Most times the fileid of the log file is 2.

4. Change the recovery model back to "full/bulk logged".

USE [master]
GO
ALTER DATABASE [SESDB]     Note: Replace <SESDB> with the name of the database.
SET recovery FULL WITH no_wait
GO

After these steps the log file size should be reduced.

Article Document

Title  How to Truncate the Transactional Log in SQL Server 2008

URL Name  How-to-Truncate-the-Transactional-Log-in-SQL-Server-2008