How to Truncate and Shrink SQL Server Transaction Logs

8.1K

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.

Our guide applies to SQL Server 2016, 2017, 2019, 2022, and SQL Server 2025. Azure SQL Database and Azure SQL Managed Instance use different log-management and backup mechanisms, so the SQL Server procedures below should not be applied to Azure SQL platforms without checking the platform-specific guidance.

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 a transaction log shrink, SQL Server removes inactive VLFs from the end of the physical log file and returns the released space to the OS. SQL Server cannot shrink the log past VLFs that are still required for active/otherwise non-reusable log records.

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);
• In Availability Groups log truncation on the primary can be delayed for different reasons on a secondary replica. A large log_send_queue_size indicates that log records have not yet been delivered and hardened on the secondary. A large redo_queue_size indicates that the secondary has received and hardened the log but has not yet applied it to the data files. A suspended/unhealthy secondary can also prevent the primary from truncating the affected portion of the log.
  In order to identify secondary replicas that may be delaying transaction log truncation, run the following:

  SELECT
  ag.name AS availability_group,
  
  DB_NAME(drs.database_id) AS database_name,
  ar.replica_server_name,
  drs.synchronization_state_desc,
  drs.synchronization_health_desc,
  drs.log_send_queue_size,
  drs.log_send_rate,
  drs.redo_queue_size,
  drs.redo_rate,
  drs.truncation_lsn,
  drs.suspend_reason_desc,
  drs.last_sent_time,
  drs.last_received_time,
  drs.last_hardened_time,
  drs.last_redone_time
  
  FROM sys.dm_hadr_database_replica_states AS drs
  JOIN sys.availability_replicas AS ar
  ON drs.replica_id = ar.replica_id
  JOIN sys.availability_groups AS ag
  ON drs.group_id = ag.group_id
  
  WHERE drs.is_local = 0
  
  ORDER BY
  drs.database_id,
  drs.truncation_lsn;

  In order to identify databases where transaction log truncation is currently delayed by an Availability Group secondary replica, use:

  SELECT
  name AS database_name,
  recovery_model_desc,
  log_reuse_wait_desc
  FROM sys.databases
  WHERE log_reuse_wait_desc = 'AVAILABILITY_REPLICA';

  In case log_reuse_wait_desc is AVAILABILITY_REPLICA, you need to identify the affected secondary replica using truncation_lsn, log_send_queue_size, and redo_queue_size before attempting to shrink the log.
• Always On Availability Groups. Transaction log truncation on the primary replica can be delayed when a secondary replica has not hardened/redone the required log records. Before shrinking a log file on an AG database, you need to check the sync state and log send/redo queues of the secondary replicas. Shrinking should not be used to work around an AG sync problem.
• Database Mirroring;
• Other features that prevent log truncation.

Accelerated Database Recovery (ADR)

Accelerated Database Recovery (ADR) was introduced in SQL Server 2019 and changed how SQL Server manages transaction rollback and row versions. When ADR is enabled, SQL Server uses a persistent version store (PVS) in the user database and maintains a separate logical sLog for transaction log info.

ADR can affect transaction log behavior and troubleshooting, so when investigating unexpected log growth/long-running transactions, you should check if ADR is enabled and review the database’s version-store usage.

ADR stores row versions in the Persistent Version Store (PVS), which is stored in the database data files rather than in tempdb. Therefore, a growing PVS can increase the size of the .mdf or .ndf files. This is separate from transaction log growth and cannot be fixed by shrinking the .ldf file.

SELECT
DB_NAME(database_id) AS database_name,
persistent_version_store_size_kb / 1024.0 / 1024 AS pvs_size_gb,
online_index_version_store_size_kb / 1024.0 / 1024 AS online_index_version_store_gb,
current_aborted_transaction_count,
oldest_active_transaction_id
FROM sys.dm_tran_persistent_version_store_stats
WHERE persistent_version_store_size_kb > 0
ORDER BY persistent_version_store_size_kb DESC;

If PVS keeps growing, you need to check for long-running transactions, long-running snapshot scans, queries on secondary replicas, and aborted transactions. These can prevent obsolete row versions from being cleaned up.

Starting with SQL Server 2022, PVS cleanup supports multiple cleaner threads. On high-DML workloads, you can use the ADR Cleaner Thread Count server config option when the default cleanup rate is insufficient.

You can use sys.sp_persistent_version_cleanup for troubleshooting/during a maintenance window when PVS cleanup is not keeping up (an active transaction can prevent cleanup from completing):

EXEC sys.sp_persistent_version_cleanup N'YourDBName';

When troubleshooting transaction log growth on SQL Server 2019 and later, you need to check if Accelerated Database Recovery (ADR) is enabled for the affected database:

SELECT
name,
is_accelerated_database_recovery_on
FROM sys.databases
WHERE database_id > 4;

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.

VLF creation depends on the size of the log growth. SQL Server uses different VLF allocation algorithms depending on the SQL Server version and the size of each growth operation.

Since SQL Server 2014, the algorithm was changed in order to reduce excessive VLF counts from smaller growth operations.

SQL Server 2022 changes the VLF creation algorithm for certain log growth events. When a log growth is more than 1/8 of the current log size but is 64 MB or smaller, SQL Server 2022 creates one VLF instead of four as in earlier versions. This reduces the number of small VLFs created by such growth operations.

Keep in mind that large growth operations can also create a large number of VLFs, so transaction log files should be sized appropriately and grown in reasonably sized increments rather than through frequent small autogrowth events.

For SQL Server 2016 SP2 and later, you can 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 command below:

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 should 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. Azure SQL Database is different: the platform automatically shrinks transaction log files as needed in most cases. Manual log shrink is generally unnecessary and is mainly relevant when allocated log storage needs to be reclaimed sooner (for example when local storage is approaching its limit in Premium/Business Critical tiers).

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];
GO

ALTER DATABASE [YourDBName] SET RECOVERY SIMPLE;
GO

SELECT
name AS logical_file_name,
physical_name,
size / 128.0 AS size_mb
FROM sys.database_files
WHERE type_desc = 'LOG';
GO

-- Replace YourActualLogLogicalName with the logical name returned above.
DBCC SHRINKFILE (N'YourActualLogLogicalName', 4096);
GO

Note that DBCC SHRINKFILE requires the logical name of the transaction log file. The logical name is not necessarily <DatabaseName>_log. You should use the value returned by the sys.database_files query above.

For example, to shrink a log file to 4 GiB (4096 MiB), use the command:

USE master;
GO

ALTER DATABASE [YourDBName]
SET RECOVERY SIMPLE;
GO

USE [YourDBName];
GO

CHECKPOINT;
GO

DBCC SHRINKFILE (N'YourActualLogLogicalName', 4096);
GO

USE master;
GO

ALTER DATABASE [YourDBName]
SET RECOVERY FULL;
GO

BACKUP DATABASE [YourDBName]
TO DISK = N'E:\Backup\YourDBName_full.bak'
WITH COMPRESSION;
GO

After switching back to the FULL recovery model, don’t forget to take a new full database backup before relying on subsequent transaction log backups for point-in-time recovery.

For FULL or BULK_LOGGED recovery, you need to back up the transaction log to make inactive space reusable (note that this does not shrink the .ldf file; use DBCC SHRINKFILE separately if the physical file is oversized):

BACKUP LOG YourDBName
TO DISK = 'E:\Backup\YourDBName.trn'
WITH COMPRESSION;

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

Important. Pay attention that our script changes the recovery model only for YourDBName, shrinks the transaction log for that database, and then switches the database back to FULL recovery. Do not run it against multiple production databases without reviewing each database’s recovery requirements and backup strategy. Switching from FULL or BULK_LOGGED to SIMPLE breaks the existing transaction log backup chain. After switching back to FULL, you need to take a new full or differential database backup before relying on subsequent transaction log backups for point-in-time recovery. You should use this approach only as an emergency operation and review each database recovery requirement before execution.

Emergency recovery-model change

Note that you should treat changing a production database from FULL or BULK_LOGGED recovery to SIMPLE as an emergency procedure, it’s not a routine transaction-log maintenance. You should use it only when the transaction log cannot be backed up/truncated and immediate disk-space recovery is required.

Before changing the recovery model, don’t forget to check the database’s current recovery model and the reason preventing log reuse:

SELECT
name,
recovery_model_desc,
log_reuse_wait_desc
FROM sys.databases
WHERE name = N'YourDBName';

If the database is in FULL or BULK_LOGGED recovery and you have enough disk space for a transaction log backup, back up the transaction log instead of changing the recovery model:

BACKUP LOG YourDBName
TO DISK = 'E:\Backup\YourDBName.trn'
WITH COMPRESSION;

If an emergency recovery-model change is unavoidable, you can use the following procedure for the specific database:

USE master;
GO

ALTER DATABASE YourDBName
SET RECOVERY SIMPLE;
GO

USE YourDBName;
GO

CHECKPOINT;
GO

DBCC SHRINKFILE (N'YourActualLogLogicalName', TRUNCATEONLY);
GO

USE master;
GO

ALTER DATABASE YourDBName
SET RECOVERY FULL;
GO

BACKUP DATABASE YourDBName
TO DISK = 'E:\Backup\YourDBName_full.bak'
WITH COMPRESSION;
GO

The final full database backup is required to establish a new backup chain after switching back to the FULL recovery model. Do not rely on transaction log backups taken before the recovery-model change as a continuous point-in-time recovery chain across that change.

Don’t forget to replace E:\Backup\ with the path used by your backup infrastructure. You need to ensure the backup destination has sufficient free space and is included in your normal backup retention and verification procedures.

Warning. For production databases using the FULL or BULK_LOGGED recovery model, take a transaction log backup to make inactive log space reusable whenever possible. Do not switch to SIMPLE recovery merely to shrink a log file.

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.

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 info 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'

The recommended method is to update the file location in SQL Server metadata with ALTER DATABASE … MODIFY FILE, take the database offline, move the physical .ldf file, and bring the database back online. This preserves the database metadata and avoids the detach/attach workflow.

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 WITH ROLLBACK IMMEDIATE;
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_log2,
    FILENAME = 'E:\mssql\data\mysqldb2.ldf',
    SIZE = 1000MB,
    MAXSIZE = 2000MB,
    FILEGROWTH = 1024MB
);
GO

Note that the detach/attach method is an older alternative and is not necessary for routine transaction log relocation. You should use ALTER DATABASE … MODIFY FILE with an offline/online cycle instead.

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.