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

Learning never stops for a consultant. In this blog, I would share another learning and discuss message: cannot shrink log file because the logical log file located at the end of the file is in use.

Here are some earlier blogs on the same topic where I received messages while trying to shrink the LDF files of the database.

• SQL SERVER – Cannot Shrink Log File Because Total Number of Logical Log Files Cannot be Fewer than 2
• SQL SERVER – Information Message: Cannot shrink file in the database to xxxx pages as it only contains yyyy pages.

The whole shrink story started from below blog.

• How to Shrink All the Log Files for SQL Server? – Interview Question of the Week #203

Many readers have sent me emails with various messages they have seen, and I am writing a blog on them.  Following is the information message which might come when you try to shrink the LDF file.

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

WORKAROUND/SOLUTION

We need to use check the status column of VLFs using

USE DATABASE_NAME
GO
DBCC LOGINFO
GO

More information can be found here.

• SQL SERVER – Detect Virtual Log Files (VLF) in LDF

Mostly you would see last VLF having status column value as 2, which means active VLF and can’t be released.  Next, we should use the following query:

select name, log_reuse_wait_desc
from sys.databases

Based on the output you need to take action and free up the active VLF located at the end. Refer SQL SERVER – How to Stop Growing Log File Too Big

Reference: Pinal Dave (https://blog.sqlauthority.com)