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.

The whole shrink story started from below blog.

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


We need to use check the status column of VLFs using


More information can be found here.

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)

Exit mobile version