Transaction logs on all SQL Server version (starting from SQL Server 2008 and up to SQL Server 2017) 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. Used to free up space in the logical log for reusing by the 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 on 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 not busy and can be used. 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 an error:
The transaction log for database is full due to ‘OLDEST_PAGE’.
In most cases, SQL Server administrators encounter transaction logs overflow in SQL if regular backup of the transaction log is not performed in the recovery models BULK LOGGED and 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. 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. 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 allows 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 also cleared until running backup.
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 this error.
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 avoids 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 in which 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 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 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 that 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).
To truncate SQL transaction logs launch 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.
After completing an operation, change database Restore mode back to Full.
Using Transact-SQL ti Truncate Transaction log
The same can be done from the SQL Query Analyzer with a simple script (script works starting from SQL Server 2008):
USE ″YourDBName″ ALTER DATABASE ″YourDBName″ SET RECOVERY SIMPLE DBCC SHRINKFILE (″YourDBName″, ″Desired_size″); ALTER DATABASE ″YourDBName″ SET RECOVERY FULL
Tip. After you truncate the transaction log and shrink 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 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 and 2016, 2017.
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 selected 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.