SQL SERVER – Error: The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is runn

Problem.

Sometimes when we are trying to truncate the transaction log, we can got the following error message:

Statement:

BACKUP LOG <SID> WITH TRUNCATE ONLY

Error:

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.

This is because some part of the transaction log is pending to replicate (publisher side), hence is not possible to truncate it from the transaction log. In this case is necessary to mark these transactions as replicated in order to be able to truncate them.

Solution / Fix / Workaround.

To mark the transactions as distributed (or replicated) we can use the following statemtent:

USE <SID>

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

Use sp_repldone just for troubleshooting purpose and in emergency situations. If this SP is not used in the proper way you can invalidate pending information to be replicated.

Sometimes when sp_repldone is used, is possible that we get the following error.

Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1 The database is not published

It means that our Database is not published. A workaround to this error, is to publish our database with the below statement, then try again the sp_repldone and finally execute again the sp_dboption with publish = false.

sp_dboption '', 'Publish', 'true'

Ex.

sp_dboption '', 'Publish', 'true'

EXEC sp_repldone ....

sp_dboption '', 'Publish', 'false'