locked
Transactional replication is not syncing during full backup RRS feed

  • Question

  • Hello,

    I have noticed that our transactional replication is not working during a full backup. It seems that the transactions are not delivered to the distribution database and the log agent keep logging such events:

    The Log Reader Agent is scanning the transaction log for commands to be replicated. Approximately 30500000 log records have been scanned in pass # 3, 1696 of which were marked for replication, elapsed time -623164036 (ms).

    After the backup is complete the issue is gone, but this still creates latency during the backup. The database is in simple recovery.

    Does anybody know if this behavior is normal and expected?
    I know that the backup is placing a mark in the log at its beginging and at last it is capturing the log generated during the backup duration, but why is this impacting the replication

    Thanks is advance.
    Friday, March 12, 2010 10:39 AM

Answers

  • Hi,

    There is a “sync with backup” option which means: Setting the sync with backup option on the publication database ensures that the Log Reader Agent will not propagate any transactions to the distribution database that have not been backed up at the Publisher. This ensures that the last backup can be restored without any possibility of the distribution database having transactions that the restored publication database does not have. So by default, the log would not be truncated until backed up done. For more information, see Strategies for Backing Up and Restoring Transactional Replication.

    More information:
    SQL Server 2008 Transactional Replication

    Thanks,
    Chunsong


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, March 19, 2010 3:22 AM

All replies

  • This normally means that there is a lot of logging being written to the log. By chance is there any defragging or reindexing going on prior to the backup?

    This is normal behavior for those types of operations.
    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Friday, March 12, 2010 2:05 PM
    Answerer
  • Hi, Hilary

    Thanks for your reply. There is only a full backup which is running at that time, but it takes a couple of hours and yes, during that time there is high activity in the database. Could you explain in more details why the backup is impacting the replication? I am interested in the internals of this issue.
    Friday, March 12, 2010 3:30 PM
  • Hi,

    There is a “sync with backup” option which means: Setting the sync with backup option on the publication database ensures that the Log Reader Agent will not propagate any transactions to the distribution database that have not been backed up at the Publisher. This ensures that the last backup can be restored without any possibility of the distribution database having transactions that the restored publication database does not have. So by default, the log would not be truncated until backed up done. For more information, see Strategies for Backing Up and Restoring Transactional Replication.

    More information:
    SQL Server 2008 Transactional Replication

    Thanks,
    Chunsong


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, March 19, 2010 3:22 AM
  • Thanks a lot! That answers the question.
    Saturday, March 27, 2010 7:30 AM