SQL SERVER – Cannot Shrink Log File 2 (SQLAuthorityDB_log) Because the Logical Log File Located at the End of the File is in Use

Cannot shrink log file is a message about what SQL Server still needs, not just how large the LDF looks. In this 2019 entry in my log-shrink series, I discussed a message readers had sent while trying to reduce a transaction log. The important clue was at the end of the file.

The Original Message

Cannot shrink log file 2 (SQLAuthorityDB_log) because the logical log file located at the end of the file is in use.

DbId   FileId   CurrentSize   MinimumSize   UsedPages   EstimatedPages
10     2        1817448       128           1817448     128

Those file and database IDs belong to the original example. Do not assume your log is file 2 or use its estimated size as your target. Identify the actual logical file name and the space the workload needs.

Truncation and Shrinking Solve Different Problems

Log truncation makes inactive portions available for reuse inside the existing file. Shrinking reduces the physical file and returns space to the operating system. A log backup can help truncation under the appropriate recovery model, but it does not itself make the LDF smaller.

SQL Server divides a log file into virtual log files, or VLFs. A log shrink removes inactive VLFs from the physical end. It cannot cut away an active VLF there, even when other parts of the log have reusable space. The achievable size also follows VLF boundaries, so a requested target is not a guaranteed result.

Check the Database and Its Log Usage

Run these read-only checks in a query window connected to the affected database. They identify the recovery model, current reuse wait and actual log files. The growth column is a percentage when is_percent_growth is 1, otherwise it is in 8 KB pages; max_size also uses pages, with special values such as -1 for growth up to the engine limit.

-- Connect to the database being investigated before running these checks.
SELECT DB_NAME() AS database_name,
       CONVERT(nvarchar(128), SERVERPROPERTY('ProductVersion')) AS product_version,
       recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE database_id = DB_ID();

SELECT file_id, name AS logical_file_name,
       CONVERT(decimal(18,2), size / 128.0) AS file_size_mb,
       growth, is_percent_growth, max_size
FROM sys.database_files
WHERE type_desc = 'LOG';

Next, compare allocated and used log space. This result combines all log files in the current database. A low used percentage does not tell you where the active VLFs sit.

SELECT DB_NAME(database_id) AS database_name,
       CONVERT(decimal(18,2), total_log_size_in_bytes / 1048576.0) AS total_log_mb,
       CONVERT(decimal(18,2), used_log_space_in_bytes / 1048576.0) AS used_log_mb,
       CONVERT(decimal(9,2), used_log_space_in_percent) AS used_log_percent
FROM sys.dm_db_log_space_usage;

For this space-usage DMV, SQL Server 2019 and earlier require VIEW SERVER STATE; SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE. Have an authorized administrator run the checks if the required diagnostic permission is not available.

Inspect the Physical End of Each Log File

The original article used DBCC LOGINFO. On SQL Server 2016 SP2 and later, use the documented sys.dm_db_log_info function instead. It requires VIEW SERVER STATE on earlier supported releases, and VIEW DATABASE PERFORMANCE STATE on SQL Server 2022 and later.

SELECT f.name AS logical_file_name, v.file_id,
       v.vlf_begin_offset, v.vlf_size_mb,
       v.vlf_active, v.vlf_status
FROM sys.dm_db_log_info(DB_ID()) AS v
JOIN sys.database_files AS f ON f.file_id = v.file_id
ORDER BY v.file_id, v.vlf_begin_offset DESC;

Within each file, the first row has the highest physical offset. vlf_active = 1 and vlf_status = 2 identify an active VLF. If the physical tail is active, SQL Server cannot remove it. Do not confuse the physical ordering here with the sequence in which log records were written.

Investigate the Reuse Wait Before Choosing an Action

The value of log_reuse_wait_desc helps direct the investigation. Recheck after the underlying condition changes; it is a changing diagnostic value, not a repair command.

• LOG_BACKUP: Check the scheduled log-backup job, its failures and destination capacity. Under full or bulk-logged recovery, use the established backup process and retain the resulting backup.
• ACTIVE_TRANSACTION: Identify the long-running or uncommitted transaction and its application owner. Do not kill a session simply to make a file smaller; rollback can be substantial.
• AVAILABILITY_REPLICA, DATABASE_MIRRORING or REPLICATION: Investigate the relevant data movement or processing backlog before changing the recovery configuration.
• CHECKPOINT: Investigate checkpoint progress and the recovery model. A checkpoint is not a substitute for required log backups.
• NOTHING: Reusable VLF space exists, but it need not be at the physical end. The active tail can still prevent the requested shrink.

Resolving one reuse wait does not guarantee that the tail immediately becomes inactive. Allow normal log activity and truncation to progress, then inspect again. Do not generate a large artificial workload merely to force log wraparound.

Keep the Log Sized for the Workload

If the log grew for recurring index maintenance, imports or large transactions, that space may be needed again. Repeatedly shrinking and regrowing it adds work without fixing the cause. Plan capacity for the normal peak, sensible growth increments and the time needed to handle a backup or replica interruption.

A one-time reduction can be reasonable after an exceptional event, once reuse is healthy and there is genuinely excess capacity. Choose a practical target and a suitable maintenance window. This article intentionally stops at diagnosis because a safe target depends on that workload.

Do not switch to simple recovery just to shrink a file. It changes recovery protection and interrupts the log-backup strategy. Returning to full recovery requires a data backup to start the new log chain and an appropriate ongoing log-backup schedule.

Other Messages in This Series

This discussion followed How to Shrink All the Log Files for SQL Server?. Treat that earlier interview article as historical context, not a routine maintenance recommendation.

• Cannot shrink a log below its minimum number of logical log files
• Cannot shrink a file because it contains fewer pages than the requested target
• Detect virtual log files in an LDF
• How to stop a log file growing too large

A smaller log is not the goal, it is a possible result after the log can be reused and the workload is properly sized.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.