Open Transactions Stuck in the Log of a Database in Simple Recovery Mode

Open Transactions Stuck in the Log of a Database in Simple Recovery Mode

The situation:

I found I had a transaction log that had grown somewhat out of control on a SQL Server 2008 server and pressing hard on what little disk space I had left on a system. I needed to shrink that log file ASAP before I had a critical outage.

I’m not going to focus on the long drawn out discussion that could be had with regards to how the log got to this condition. I only plan to document the condition of said database and how I corrected the problem. So…..YMMV


The facts:

  • The database was in SIMPLE RECOVERY Mode
  • The log was not automatically truncating (marking the inactive VLFs to be re-used)
  • DBCC OPENTRAN showed NO Active Transactions
  • DBCC LOGINFO showed 187 VLFs with STATUS = 2 and 2 VLFs with STATUS = 0



Essentially, I had active transactions stuck in the log and no open transactions to correlate to them. Again, not going to cover how that could have occurred here. I simply needed the VLFs containing those transactions to become inactive. I needed to be able to clear an unruly transaction log to get going again.


What I did:

Prior to SQL Server 2008, again in an emergency sitaution, I would have leveraged the BACKUP LOG WITH TRUNCATE_ONLY command, but Microsoft removed that from 2008 and BOL recommends you simply change your database recovery model from FULL to SIMPLE, then back to FULL and take a full backup to restart your backup/log chaining. Great, but what if my database was already in SIMPLE mode? Well, I found the same advice will help in this situation.

The Big Warning
Although there is little chance of corruption or damage to the database, performing the steps below on a production system should only be done in an emergency situation. What I’m documenting here is a modified attack from what is found in BOL (Books Online) for a SQL 2008 system. For 2000/2005, you have the option of using the BACKUP LOG WITH TRUNCATE_ONLY to essentially do the same thing, but again, proceed with extreme caution.

Executed the following:

ALTER DATABASE Database_Name SET RECOVERY FULL;
 
ALTER DATABASE Database_Name SET RECOVERY SIMPLE;
 
USE Database_Name
GO
 
CHECKPOINT;



This effectively cleared all the uncommitted/active transactions from the log and I was then able to shrink the log back down to it’s original size.

Hope that helps someone out there. But again, be sure you understand what you’re doing here. And if you’re not sure, seek additional advice.

-Patrick

Leave a Reply

Your email address will not be published. Required fields are marked *