Sql-server – Replication fails with “Transaction log for database is full due to LOG_BACKUP”

replicationsql serversql server 2014transaction-logtransactional-replication

Replication is failing with the subject error.

The log_reuse_wait_desc states LOG_BACKUP.

I have performed both database and log backups.

After changing the recovery mode to single, performing backup, and then changing recovery mode back to FULL, log_reuse_wait_desc goes to NOTHING.

As soon as I start the replication jobs they fail with the subject error.

I did shrink on log file to no avail. Any suggestions to resolve this?

Best Answer

there is no clear indication of what may be causing this upsetting in the transaction log.

but I would check it out what is happening in the distribution.

run the following procedure:

sp_helpdistributor

that will show you where the distribution database and server is.

Assuming the name of the distribution database is distribution:

 SELECT * FROM distribution.dbo.MSrepl_Commands

have a look at what you get and use the last transaction to see what is in it:

enter image description here

check it out:

 SELECT * FROM distribution.dbo.MSrepl_Commands 
--- commands for all transactions for replication for seqno or all
  WHERE   xact_seqno = 0x000002470000F7D30021

if all good or at least nothing obviously bad: use that xact_seqno here (I did not use the end one on this example - but if your replication is very busy you can limit it):

    exec distribution.dbo.sp_browsereplcmds @xact_seqno_start =  '0x000002470000F7D30021',  -- returns all/seqno info (sql query) on replication
   -- @xact_seqno_end  =  '0x00001C030000F4540018'

This allows you to see what has been replicated - what commands to each article (see on the right hand side of the picture below)

enter image description here

This is not a comprehensive answer but would give you a start. Well after the obvious thing as looking at the replication monitor.

Check these scripts to give you some extra vision.

You can also check Some useful commands and sp for Transaction Replication

Related Question