Unable to shrink the transaction log

 

Symptom
I was not able to shrink the transaction log of my database. I got the following error message when I ran an DBCC SHRINKFILE (N'LogicalName' , NOTRUNCATE)

Cannot shrink log file 2 (XXLogicalNameXX) because all logical log files are in use.
Cannot shrink log file 2 (XXLogicalNameXX) because the logical log file located at the end of the file is in use.

Environment: my database was a publisher of my transactional replication and the recovery model was in SIMPLE.

Troubleshooting step
Step 1: if your recovery model is FULL, be ensuring that you made backup log. BACKUP LOG databaseName TO DISK='C:\fileName.TRN'

Step 2: Check the log space used with the command dbcc SQLperf(logspace). Do you have a free space?

Step 3: use the DBCC OPENTRAN Transact-SQL to verify if there is an active transaction in a database at a particular time. If yes kill it.

Step 4: check the value of log_reuse_wait_desc

select name, database_id,recovery_model_desc,log_reuse_wait_desc from sys.databases where name LIKE 'yourDatabaseName'

Cause
In my case, column log_reuse_wait_desc returned REPLICATION (e.g. BOL Factors That Can Delay Log Truncation). So the log was not truncated because records at the beginning of the log are pending replication.

Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed. Typically the Log Reader agent will parse the entire log and then mark each log record as replicated by executing sp_repldone.

Resolution
When I tried doing the same manually, my issue was fixed:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

With sp_repldone, all replicated transactions in the log are marked as distributed. This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log.

If you execute sp_repldone manually, you can invalidate the order and consistency of delivered transactions. So if you are not aware with her impact, I recommend you to drop your Publication, Subscription and Disabled Replication. Then run the shrink command and recreate the replication.

Reference
- A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
- How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server
- Transaction Log Truncation
- Shrinking the Transaction Log
- How to use the DBCC SHRINKFILE statement to shrink the transaction log file

 

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |