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.
Table of Contents
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:
DBCC LOGINFO
The number of returned lines indicates how many 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’.
or
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
Or
Action failed for Database MSSQL (Microsoft.SqlServer.Smo) An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo) The transaction log for database MSSQL is full due to “LOG_BACKUP” (Microsoft Sql Server, Error 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).
When error 9002 occurs, all pending transactions are rolled back and SQL Server stops.
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.
How to Move Transaction Log Files to Another Drive?
If you cannot expand the drive where the transaction log (LDF) is stored, you can move it to another drive that has enough free space. Unfortunately, log will require you to detach and attach a database when moving (your SQL database will be unavailable for some time).
Run the following command to get the current location of the transaction log file and its maximum size:
select file_id, type, type_desc, name, physical_name, state, state_desc, size from sys.database_files
In our example, the database has one transaction log file with the path E:\msdb\mysqldb.ldf.
To get information about the current size of the transaction log file and its usage percentage, run the T-SQL command:
declare @logSpace table (
dbName varchar(100),
logSizeMB float,
logSpaceUsed float,
status int
)
insert into @logSpace
execute('dbcc sqlperf(''LogSpace'')')
select * from @logSpace where dbName = 'mysqldb'
You can also find out the current path to the transaction log in the database properties using MS SQL Management Studio:
# Change the database to single user mode Use MASTER GO ALTER DATABASE mysqldb SET SINGLE_USER GO # Detach the DB sp_detach_db 'AdventureWorks' GO
Move the transaction log file to a new drive using File Explorer or any file manager. If the transaction log size is very large, the move task may take a long time.
Hint. Make sure the new drive has enough free space (be sure to check if the NTFS compression is not enabled on the target disk).
# Attach the database sp_attach_DB 'AdventureWorks', 'F:\msdb\mysqldb.mdf','M:\msdb\mysqldb.ldf' GO
If a long downtime of the SQL Server database is not acceptable, you can add a new log file to the database on another drive with enough free space. To add an additional log file, use the ALTER DATABASE [dbname]ADD LOG FILE command.
For example, here we will add an additional log file on another disk for the mysqldb database:
ALTER DATABASE mysqldb ADD LOG FILE ( NAME = mysqldb, FILENAME = 'E:\mssql\data\mysqldb2.ldf', SIZE = 1000MB, MAXSIZE = 2000MB, FILEGROWTH = 5% ); GO
Microsoft doesn’t recommend using multiple log files for a single database as a long-term solution. This solution will help you quickly start the database if you run out of disk space with transaction logs. After you investigated the reason why the transaction log is full and cannot be truncated, you should disable such a file.
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.
