How to Truncate and Shrink SQL Server Transaction Logs

8K

Microsoft SQL Server transaction logs tend to grow over time and can sometimes fill all the free space on the server drive. To avoid this, you should take regular transaction log backups or use transaction log truncate and shrink operations in SQL Server.

Difference Between SQL Server Log File Truncate and Shrink

You need to be familiar with the recovery models in SQL Server before you can understand the difference between truncate and shrink operations:

• Simple recovery model — SQL Server marks inactive virtual log files as reusable after checkpoint and when no factors (such as backups/active transactions) block truncation. This recovery mode is for development or test environments only and is not recommended for production;
• Full recovery model — the transaction logs won’t be cleared until a backup of the transaction log has been completed (no automatic log truncation). The transaction log will be truncated only after backup transaction log. This mode is the best way to recover data after a failure and is used when point-in-time recovery is required.
• Bulk logged — this mode allows to reduce the log space usage by using minimal logging settings. The SQL Server truncates the transaction log only after a successful transaction log backup.

Full SQL Server database backup does not truncate the transaction log. Configuring regular transaction log backup is the only valid way to truncate log files. The truncate operation makes the space available again, but does not reduce the size of the transaction log file on the disk.

Shrinking should be used to reduce the size of the SQL Server transaction log on the drive. During the shrink operation, MSSQL moves allocated pages within the log file to free space and deallocates unused VLFs at the end of the file. The free space is then deallocated and returned to the file system.

Check transaction log space usage for databases using T-SQL command

You can check the transaction log space usage 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.

Note. On SQL Server 2016 and later, we recommend using the sys.dm_db_log_space_usage DMV for database-specific transaction log usage information:

SELECT *
FROM sys.dm_db_log_space_usage;

Why Transaction Logs Keep Growing

Before truncating/shrinking a transaction log, you should identify why the log continues to grow. Here are the common causes:

• Missing transaction log backups in Full or Bulk-Logged recovery models;
• Long-running or uncommitted transactions;
• SQL Server replication;
• Change Data Capture (CDC);
• Availability Groups. The following command shows current log send and redo queue status for databases in an Always On Availability Group:
  SELECT *
  FROM sys.dm_hadr_database_replica_states;
• Database Mirroring;
• Other features that prevent log truncation.

In order to check whether an open transaction is preventing log truncation, run the following command:

DBCC OPENTRAN;

The command above displays the oldest active transaction in the database and can help you to identify why the transaction log cannot be truncated.

Checking Why SQL Server Cannot Reuse Transaction Log Space

Before truncating/shrinking a transaction log, you should determine why SQL Server cannot reuse log space. To do this, run the following query:

SELECT name,
log_reuse_wait_desc
FROM sys.databases;

The log_reuse_wait_desc column shows the reason why transaction log space cannot be reused.

Here are the common values:

• LOG_BACKUP — a transaction log backup is required;
• ACTIVE_TRANSACTION — an open transaction is preventing log truncation;
• REPLICATION — replication is holding log records;
• AVAILABILITY_REPLICA — an Availability Group replica has not processed the log;
• DATABASE_MIRRORING — database mirroring is delaying log reuse.

This information helps you to identify the root cause of transaction log growth before performing shrink operations.

Check Transaction Log VLF Count

SQL Server transaction logs are internally divided into Virtual Log Files (VLFs). Excessive log growth and frequent autogrowth operations may create thousands of VLFs, which can negatively affect startup, recovery, backup, and restore performance. Before shrinking a transaction log, you should check the current VLF count.

For SQL Server 2016 SP2 and later, use the following:

SELECT *
FROM sys.dm_db_log_info(DB_ID());

In order to display the number of VLFs in the current database, use the following:

SELECT COUNT(*) AS VLFCount
FROM sys.dm_db_log_info(DB_ID());

In case you are using older SQL Server versions, use the following:

DBCC LOGINFO;

In case the transaction log contains an excessively large number of VLFs, shrinking and then regrowing the log in appropriately sized increments may help you to reduce VLF fragmentation and improve transaction log performance.

How to Truncate Transaction Logs on MS SQL Server

If the size of the transaction log files (*.ldf) is fixed, or if there is insufficient disk space, all operations to modify the database will be unavailable. You may encounter the following errors when connecting to an MS SQL database:

The transaction log for database is full due…

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.

This is the situation that typically occurs when a full recovery model is used. To free up space, you should back up the transaction logs (the safest method), or you can delete the logs by changing the DB recovery mode to Simple.

Warning. Note that you shoul switch a production database from the Full recovery model to Simple only as an emergency operation when transaction log backups are unavailable. Keep in mind that this breaks the transaction log backup chain and prevents point-in-time recovery (until a new full database backup is performed).

Change MS SQL Server recovery model on the fly

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 to read-only mode.

Open the SQL Server Management Studio (SSMS), select the database (with large transaction logs), right-click and select Properties. Go to Options and switch the database Recovery model to Simple.

In this case, the transaction logs are automatically truncated, but still take up a lot of space as the truncated space will not be deallocated. To reduce the size of the logs, you can also shrink the files.

Then right-click DB, select 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.

Shrink options

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 or Bulk-Logged.

WARNING! This allows you to quickly reduce the file size of transaction logs. But it results in the loss of transaction records since the last backup. This is why you should perform a full DB backup as soon as possible.

Enable Auto Shrink option

Note that Auto Shrink is generally not recommended for production databases.

Auto Shrink should generally be avoided and you should consider it for small non-production databases. Go to the DB Options > and set the Auto Shrink parameter value in the Automatic section to True. After you enable auto shrink, MS SQL will only perform automatic compression if the unused space is more than 25% of the total volume size. However, shrinking transaction files is better than shrinking data files.

Using Transact-SQL Truncate Transaction Log

Note. For production databases running in the Full recovery model, you should use backing up the transaction log as the preferred method of truncating log records. You should consider switching to the Simple recovery model only for emergency space recovery scenarios.

Before executing any recovery model/shrink operations, you need to make sure that a full database backup exists for each user database.

You can also switch the MS SQL DB to simple recovery mode and shrink the log file using T-SQL script:

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:

USE YourDBName;
GO

ALTER DATABASE YourDBName
SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE (YourDBName_log, 4096);
GO

ALTER DATABASE YourDBName
SET RECOVERY FULL;
GO

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 DISK = 'E:\Backup\YourDBName.trn';

Note that BackupDevice must be pre-created using sp_addumpdevice, or you can use TO DISK for direct backup to a file.

Warning. Note that this script changes all user databases from Full/Bulk-Logged recovery model to Simple, shrinks their transaction logs, and then switches them back to Full recovery. Keep in mind that running this script breaks the transaction log backup chain and makes point-in-time recovery impossible until a new full database backup is created. You should use this approach only as an emergency operation and review each database recovery requirement before execution.

In emergency situations, you can use the following script to switch all user databases to the Simple recovery model and shrink their transaction logs. Review the recovery requirements of each database before using this approach:

DECLARE @db_name nvarchar(100);
DECLARE @logname nvarchar(255);
DECLARE @sql nvarchar(max);

DECLARE cursor_size_srv CURSOR FOR
SELECT name
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

    SELECT @logname = mf.name
    FROM sys.master_files mf
    WHERE mf.database_id = DB_ID(@db_name)
    AND mf.type_desc = 'LOG';

    SET @sql = '
    USE [' + @db_name + '];

    ALTER DATABASE [' + @db_name + '] SET RECOVERY SIMPLE;

    DBCC SHRINKFILE (' + QUOTENAME(@logname) + ', 10, TRUNCATEONLY);

    ALTER DATABASE [' + @db_name + '] SET RECOVERY FULL;
    ';

    EXEC sp_executesql @sql;

    FETCH NEXT FROM cursor_size_srv INTO @db_name;
END;

CLOSE cursor_size_srv;
DEALLOCATE cursor_size_srv;

Note that when using TRUNCATEONLY, the target size parameter is ignored. SQL Server only frees unused space at the end of the log file and does not guarantee reduction to a specific size.

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).

The recommended approach is to update the file metadata using ALTER DATABASE … MODIFY FILE, take the database offline (or stop the SQL Server service during a maintenance window), move the log file, and then bring the database back online.

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'

Step 1. Change the log file path in SQL Server metadata

USE master;
GO

ALTER DATABASE mysqldb
MODIFY FILE
(
NAME = mysqldb_log,
FILENAME = 'M:\msdb\mysqldb.ldf'
);
GO

Step 2. Take the database offline

ALTER DATABASE mysqldb
SET OFFLINE;
GO

Step 3. Move the .ldf file

Move the transaction log file to the new location using File Explorer or another file management tool.

Step 4. Bring the database online

ALTER DATABASE mysqldb
SET ONLINE;
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, we are going to add an additional log file on a different 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

Note that the detach/attach method still works and may be useful in some recovery case, but Microsoft generally recommends using ALTER DATABASE … MODIFY FILE for routine file relocation operations (because it preserves database metadata and reduces operational risk).

Note. Microsoft doesn’t recommend using multiple log files for a single database as a long-term solution. This solution allows you to quickly start the database when you run out of space on the transaction logs drive. After you investigated the reason why the transaction log is full and cannot be truncated, you should disable such a file.