Transaction logs on all SQL Server versions (starting from SQL Server 2008 and up to SQL Server 2019) tend to grow over time, which can sometimes fill all the free disk space on the server. To avoid this, SQL Server has a transaction log truncate operation. The truncation process is used to free up space by removing all inactive virtual log files (VLFs) from the SQL Server transaction logs.
SQL Server Transaction Logs and DB Recovery Model
Note. The transaction logs are used to record all transactions before committing data to the DB data file. The transaction log files are required to roll back the database to the previous state. Typically, the journal stores the sequence number, type of change, the operations performed, etc. Most likely, you can use your SQL Server without the transaction log, but then there is the possibility to lose the database in the future in case of failure.
A transactional log consists of small logical elements called VLF (Virtual Log File). You can find out their number by executing the following query in the context of the SQL Server database:
The number of returned lines indicates how much virtual files the log is segmented. The Status field indicates the current status of the segment. A value of 0 means that the segment is currently free and can be reused. 2 means that the segment is in use. If there are no free segments, and the transaction log growth is allowed in the settings of the SQL Server database, it will be increased and new VLFs will be created. If the size of the transaction log is fixed, or there is not enough disk space, then all operations to modify the database structure or its contents will become unavailable. Most likely, you will get one of the following errors:
The transaction log for database is full due to ‘OLDEST_PAGE’.
ODBC error: (42000) – [Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database ‘database’ is full. Back up the transaction log for the database to free up some log space.
In most cases, SQL Server administrators encounter transaction logs overflow in SQL if a regular backup of the transaction log is not performed, and the recovery model is BULK LOGGED or FULL.
Log files truncate automatically, depending on the recovery model used in your SQL Server settings:
- Simple recovery model — log files truncate automatically after reaching checkpoint (the simplest option that requires database administration). When using the Simple recovery model, the transaction log is cleared immediately after the transaction is completed. The truncation process is automatic. In this mode, you can roll back your DB only to the database full backup creation time;
- Full recovery model — the transaction log won’t be cleared until a backup of the transaction log is completed. No automatic log truncation, the transaction log will be truncated only after backup transaction log: BACKUP LOG dbname TO DISK = ‘dblog.bak’ . This mode provides the best possibility of data recovery after a failure. In the Full mode, the transaction log (LDF) can grow (because the database changes are accumulated in this log). In the Full recovery model, all SQL transactions are written to the log files on disk and stored there until the backup is created. Storing logs allow you to return to an earlier copy of the database if necessary, and you can perform a restore for each transaction. In this case, Full backup is restored and then you can roll up the logs to the time you need (the database can be restored at almost any point in time);
- Bulk logged — this mode allows to reduce the log space usage by using minimal logging settings. In this mode, the transaction log files are also cleared until running a backup, and also there is no automatic log truncation.
When the Full recovery model is used for the database, it is necessary to back up the transaction log regularly. Otherwise, it will grow excessively until it takes up all the disk space and SQL Server starts reporting the error.
You can check the transaction log space usage statistics for all databases using the following T-SQL command:
DBCC SQLPERF (LOGSPACE); GO
- Log Size (MB) – shows the current size of transaction log for the DB;
- Log Space Used (%) – shows the percentage occupied by the transaction in the log file.
Tip. To ensure SQL Server database high performance, it is recommended to place the transaction logs on separate disks with RAID 1 level. Also, database best practice recommends to pre-allocate space for transaction log files. This will help to avoid unnecessary auto-growth events.
But there are situations when automatic SQL log truncate job for some reason doesn’t work and logs occupy all available disk space. It always happens suddenly in situations when you are urgently in need of free space. In this case, you can find *.ldf log files of a very large size on the disk.
How to Truncate Transaction Logs on MS SQL Server?
In this case, this error appears when you are connecting to MS SQL database:
Microsoft OLE Provider for SQL Server: The transaction log for database “YourDBName” is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column is sys.database
HRESULT=80040E14, SQLSTATE=4 2000, native=9002
This means that the drive, where the SQL transaction log stored, is out of space and SQL cannot write a new transaction data. In this case, you can truncate SQL logs files manually (using the SQL query or from the Management Studio GUI).
Tip. You need to backup transaction logs on a productive SQL server regularly to avoid overflowing the transaction log file and automatic growth operations.
This situation typically occurs when using a Full recovery model. In this model, the log files cannot be cleared until all transactions are not present in the backup. It is necessary to ensure you are using a continuous log sequence number (LSN) in the log records. Accordingly, for the truncate, you need to make a full backup of the DB, or (easier and faster) temporarily switch it to Simple recovery mode. It is possible to change the recovery model of MS SQL Server on the fly, but to reduce the risks, it is desirable to switch the database into read-only mode and perform a backup of the transaction log (if it’s possible).
In order to truncate SQL transaction logs, open the SQL Server Management Studio (SSMS), select the desired database (with large transaction log), right click on it, and select Properties from the context menu. Go to Options and switch the database Recovery model to Simple.
Then, in the same context menu, go to section Tasks > Shrink > Files. In File type select Log, in File name field specify the name of the log file. In Shrink action choose Reorganize pages before releasing unused space, set the desired size of the file, and click OK.
You can find three shrink options here:
- Release unused space — this option will reclaim unused space in the transaction log file and shrink the file to the last allocated extent. Allows to reduce the file size without moving data;
- Reorganize pages before releasing unused space — reclaims unused space and tries to relocate rows to unallocated pages;
- Empty file by migrating the data to other files in the same filegroup — is used to move all data from the specified file to other files in the same filegroup. The empty file will be removed later.
After completing an operation, change the database Restore mode back to Full.
Using Transact-SQL Truncate Transaction Log
The same can be done from the SQL Query Analyzer with a simple script (script works in all Microsoft SQL Server versions starting from SQL Server 2008):
USE ″YourDBName″ ALTER DATABASE ″YourDBName″ SET RECOVERY SIMPLE; GO DBCC SHRINKFILE (″YourDBName_log″, ″Desired_size_in_MB″); --
For example, to shrink a log file to 4 GiB (4096 MiB), use the command:DBCC SHRINKFILE(MyDatabase_Log, 4096)
ALTER DATABASE ″YourDBName″ SET RECOVERY FULL GO
Tip. You can find out the logical name of the log file with the following SQL query:SELECT name FROM sys.master_files WHERE type_desc = 'LOG'
Be sure to back up your database in the Full recovery model.
Another way to shrink the SQL transaction log is to backup the database logs with the command:
BACKUP LOG YourDBName TO BackupDevice
Tip. After you truncated the transaction log and shrinked it, be sure to make a full backup of your database.
To automatically switch all databases (except system ones) into the Simple recovery mode and execute transaction logs shrinking, you can use the following Transact SQL script:
declare @db_name nvarchar(100) declare cursor_size_srv cursor for SELECT name AS DBName FROM sys.databases where name not in ('tempdb','master','msdb','model) ORDER BY Name; OPEN cursor_size_srv FETCH NEXT FROM cursor_size_srv INTO @db_name WHILE (@@FETCH_STATUS=0) BEGIN exec ('declare @logname nvarchar(100) USE [' + @db_name + '] SELECT @logname = name FROM sys.database_files where type = 1 ALTER DATABASE ' + @db_name + ' SET RECOVERY SIMPLE DBCC SHRINKFILE (@logname , 10, TRUNCATEONLY)') ALTER DATABASE ' + @db_name + ' SET RECOVERY FULL FETCH NEXT FROM cursor_size_srv INTO @db_name END CLOSE cursor_size_srv DEALLOCATE cursor_size_srv
In addition, you can find the option “Auto Shrink” in your MS SQL database properties. When you enable this option, the SQL Server will be periodically checking the unused space and reducing the size of the database and log files. Microsoft does not recommend using this option for typical databases, and if you decided to use Auto Shrink, your database should be running in the Full Recovery mode.
You can enable this option in the database parameters in the Automatic section. Just change the Auto Shrink parameter value to True. After you enabled autoshrink, MS SQL will perform automatic compression only if the unused space occupies more than 25% of the total volume size.
This method is applicable to all supported versions of SQL Server: 2005, 2008, 2012, 2014, 2016, 2017, and 2019.
This is just one of the ways to quickly reduce the size of the logs. Not the greatest, but very simple and effective.
Also, keep in mind that when you truncate SQL transaction logs by this guide, all previous backups made in the Full Recovery model can be discarded. That’s why this method should be used only in critical cases when it is not possible to clean up the disk space in other ways.
In the training courses, Microsoft recommends that only the Full mode should be used for productive databases. However, many advanced administrators deliberately set up Simple recovery mode for their databases. In this case, there is a significant increase in performance for mass insert operations and when working with large binary data, which justifies some decrease in the possibilities of backup and recovery. What is more important for your task — additional recovery options or maximum performance, so decide for yourself.
In this article we looked on how to clear the SQL Server Transaction Log files in order to quickly free up disk space on your SQL Server host.