2

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?

3
  • Which database is being reported as having a full transaction log, the publisher, subscriber or distribution database? – HandyD Mar 16 '20 at 22:31
  • Also, shrinking the log files makes it worse. You make the "bucket" for your log records smaller, causing it to become full sooner. – Tibor Karaszi Mar 17 '20 at 7:46
  • The Publisher database log is full – Rick Mar 17 '20 at 11:27
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.