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.

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

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.

Solarwinds

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.

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – Measuring the Length of VARCHAR and NVARCHAR Columns with COL_LENGTH
Next Post
SQL SERVER – Copy Data From SSMS Query Result to Excel

Related Posts

Leave a Reply

Menu