SQL Server – “Cannot shrink log file because the logical log file located at the end of this file is in use”


As an occasional DBA and full time troubleshooter, I have had a number of complex interactions with SQL server. Most of the time, I do not blog about them, In this instance I decided to document the solution to this problem for everyone, but also in case I need it again in the future.

Microsoft SQL Server

Microsoft SQL Server

I have an SSRS site (Sql Server Reporting Services version 2014) That has been running for a few years. I had a maintenance job running to take full backups of the databases and the report server database. I had forgotten to ever setup a transaction log file backup and the transaction log was over 300 GB in size. So, I ran a transaction backup on the SQL server, this took over 2 hours, not sure the exact time since I left before it finished.

Then I wanted to shrink the transaction log file, I looked up the syntax via this Microsoft Article.  Based on the article, I used the following in my Query Window.  DBCC SHRINKFILE (‘reportserver_log’) It came back with a minimum size column of 70MB.

So then I did the Query Window DBCC SHRINKFILE (‘reportserver_log’ , 100) – It needed 70 Megabytes so I used 100.

SQL RecoveryModel

SQL Server 2014 showing Recovery Model

I received the error “Cannot shrink log file reportserver_log because the logical log file located at the end of the file is in use”.  How did I fix this mess?  I used the simplest method to .  I changed the recovery model to simple.

Then I ran DBCC SHRINKFILE (‘reportserver_log’ , 100) – It needed 70 Megabytes so I used 100, the command completed successfully.  Then I changed the recovery back to full. – Problem Solved!

 

That got me to thinking, what happens if your DB is doing transaction log shipping or it was a critical database that needed to be up and running at all times?  I did a few google searches and found this option from this very useful blog.

I did not run this code so test it first…, but it should work if you need to keep a transaction log in full recovery mode.

dbcc opentran ([YourDatabase])
GO
CHECKPOINT
GO
USE [YourDatabase]
GO
DBCC SHRINKFILE (‘YourDataBaseFileName’ , size)
GO