Bloated Transaction log in Simple recovery mode will not shrink
Received a request today from a Virtual-DBA customer asking XTIVIA to help troubleshoot why a database which is only 50MBs in size would have a transaction log of 181GB. My first thought fell to an open transaction which was never closed. But a DBCC ShrinkFile command offered no solution. At which point my colleges over the cube wall overheard me talking out loud to myself and decided to get in on the fun of troubleshooting the issue.
The first command issued:
Select * from sys.database so we could see the status of the log_resuse_wait_desc field.
Much to our surprise the field was set to “Replication”. Now, there is no distributor database, no publications, and no subscriptions to be found on this server. And yet the database seemed to think it was being replicated. And most likely at some point in it life, it was.
Our next step was to issue the following command:
DBCC Opentran
Information returned:
Oldest distributed LSN : (0:0:0) Oldest non-distributed LSN : (54682:445:1)
Sure enough the database seemed to think it was supposed to be replicating data somewhere. And since the replication was not occurring, the transactions were staying in the transaction log waiting the “all clear”, thus causing the transaction log to continue to grow and never overwrite itself.
The solution:
After a couple of quick google searches, it was found the best way to clean this up is create a new publication on the database (we limited it to just one table). Making sure not to have the snapshot agent run. Once it was created, we immediately deleted the publication and replication was cleaned up successfully.
We issued Select * from sys.database again so we could see the status of the log_resuse_wait_desc field and it was now set to “Active_Transaction”
A quick DBCC Shrinkfile command on the transaction log, and the large transaction log was no more.