How to Clear SQL Server Transaction Log to Regain Space

Andrew Jackson | December 21st, 2015 | SQL Transaction Log |

A Transaction Log is a file that contains all the records of transactions and the database modifications made by each transaction in SQL Server database.The log file plays a very important part of SQL Server database when it comes to disaster recovery and it should not be in corrupted state. In case of any disaster, we can recover the database with the help of information present in SQL Server transaction log. The transaction log should be truncated or cleared regularly to keep the size of log file from filling up. This page will be discussing on how to clear SQL Server Transaction Log.

Why To Clear SQL Server Transaction Log?

During SQL Server work, the transaction log grows if any database changes occur. The regular management of the size of transaction log is necessary to prevent the transaction log from becoming full. Log truncation or clear SQL Server transaction log is required to keep the log from filling up. The truncation process deletes inactive virtual log files from the logical transaction log, freeing space to be reused by the physical transaction log. The transaction log would eventually fill all the disk space allocated to its physical log files, if it is never truncated.

Three recovery models are there in SQL Server. Depending on which one is used, truncation process differs:

Simple Recovery Model The transaction log backups are not supported and the truncation process is automatic and space is available for use.

Bulk-logged Recovery Model No automatic log truncation, backups are needed regularly to mark unused space and make it available for overwriting. Log size can be reduced by using minimal logging for bulk operations.

Full Recovery ModelThe truncation process is same as Bulk-logged Recovery model. There is a high chance of growing log file since every transaction that takes place on the database,is logged into it.

The transaction log space can be monitored using command:

SQLPERF

How to Clear SQL Server Transaction Log?

Log truncation frees space in the log file for reuse. Therefore, it is also called Log clearing. The transaction Log file is logically divided into small chunks called Virtual Log Files (VLF). Each VLF file is the unit that can be marked as available for reuse (free) or not available for reuse (used). The VLF is marked ‘active’ if it is used and ‘inactive’ if it is free. Clear SQL Server transaction log means searching and making VLFs as free.

Note: It should be kept in mind that Log Truncation is not meant to reduce the size of the physical log file. Log Shrinking is needed to reduce the physical log file.

Clearing SQL Server transaction log involves two steps. Firstly, we need to perform log backup with TRUNCATE_ONLY option and next step is to use the DBCC SHRINKFILE function to shrink file to the required size.

BACKUP LOG WITH TRUNCATE_ONLY is not a good option as it empties all the contents of our transaction log without backing it up. Many people use this command before shrinking the log file with DBCC SHRINKFILE freeing up the drive space. TRUNCATE_ONLY is not available in later versions of SQL Server.

Truncate Only

Instead of truncating transaction logs, we can use simple recovery mode by which we do not generate logs we would not be using.

Recovery Model

We have two options to shrink the log. They are:

Shrink SQL Server Log File using SQL Server Management Studio

Right click on the database and choose:Tasks followed by Shrink and then select Files:

Select Files

Change the file type to log.

File Type

Shrink SQL Transaction Log File using T-SQL

For Simple Recovery, following command will be used:

DBCC SHRINKFILE (LogFileName, Desired Size in MB)

For Full Recovery (Only when we don’t mind losing data in log file), the commands to be used are –

ALTER DATABASE nameDB
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (LogFileName, Desired Size in MB)
GO
ALTER DATABASE nameDB
SET RECOVERY FULL

Another option to shrink SQL transaction log file is to, backup the database log using the following command:

BACKUP LOG nameDB TO BackupDevice

This is how one can get to know how to clear SQL Server Transaction Log file in order to free up the space in it for further storage of transactions. Transactions are very important to keep track of the changes taking place in corresponding database as well as to rollback just in case it is required. Therefore, always keep a check and maintain the transaction log files.

Transaction Log Restore