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
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 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