Using DBCC SHRINKFILE to shrink the transaction log Errors with 'Cannot shrink log file 2 (LogFileName) because all logical log files are in use.'

I recently had a (relatively small, 4gb) database blow out with a transaction file of 160gb+. No matter what I tried I couldn’t truncate the logs and as a result I couldn’t shrink the file. Whenever I tried to run a DBCC SHRINKFILE to shrink the transaction log. SQL would error after a short time with ‘Cannot shrink log file 2 (LogFileName) because all logical log files are in use.’

I recently solved this issue and it was a doozy; I found that in sys.databases, log_reuse_wait_desc was equal to ‘replication’. Apparently this means something to the effect of SQL Server waiting for a replication task to finish before it can reuse the log space.

So we fire the following query to find the cause of the Transaction Log growth and observe the following

select log_reuse_wait_desc,name from sys.databases

log_reuse_wait_desc                                          name

———————————————————— ————-

REPLICATION                                                                             test

And to our surprise the log_reuse_wait_desc column reflects REPLICATION for a particular database, however replication had never been used on our DB nor on our server.

So to confirm this we fire the DBCC OPENTRAN which gives the undistributed LSN for the Transaction log and we get an undistributed LSN value as shown below

DBCC OPENTRAN(13) 

 Transaction information for database ‘test’.

Replicated Transaction Information:

        Oldest distributed LSN     : (0:0:0)

        Oldest non-distributed LSN : (43831:51:1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So this confirms that above undistributed transaction is causing the Log Growth

Now in theory, you should be able to clear the state by running ‘sp_removedbreplication’; however for me ‘sp_removedbreplication’ didn’t solve the issue. Instead SQL just returned saying that the Database wasn’t part of a replication…

I found my answer here:

* http://www.sql-server-performance.com/forum/threads/log-file-fails-to-truncate.25410/
* http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx

Basically I had to create a replication, reset all of the replication pointers to Zero; and then delete the replication I had just made.
i.e.

Execute SP_ReplicationDbOption {DBName},Publish,true,1
GO
Execute sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
GO
DBCC ShrinkFile({LogFileName},0)
GO
Execute SP_ReplicationDbOption {DBName},Publish,false,1
GO

And I hope that this helps somebody else out there!

Leave a Reply

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