locked
Transactional Replication Question RRS feed

  • Question

  • We are implementing Transactional Replication on a SQL 2005 x 64 server to update a subscriber database every 2 hours. We backup the Tranaction log of the Publisher database every hour which truncates the Tranaction log. Will this affect our updating our subscriber database and keep them in sync.
    Monday, May 2, 2011 5:46 PM

Answers

  • No, replication works with all recovery models and can only "truncate" commands that have been read by the replication process.

    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
    • Proposed as answer by Peja Tao Wednesday, May 4, 2011 8:44 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, May 10, 2011 1:51 AM
    Monday, May 2, 2011 6:21 PM
    Answerer
  • Hi

     If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database. If the transaction log is growing too large, and the Log Reader Agent is running on a scheduled basis, consider shortening the interval between runs. Or, set it to run in continuous mode. If it is set to run in continuous mode (the default), ensure that it is running.

     

    Reference:BOL

    • Proposed as answer by Peja Tao Wednesday, May 4, 2011 8:44 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, May 10, 2011 1:51 AM
    Monday, May 2, 2011 9:11 PM

All replies

  • No, replication works with all recovery models and can only "truncate" commands that have been read by the replication process.

    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
    • Proposed as answer by Peja Tao Wednesday, May 4, 2011 8:44 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, May 10, 2011 1:51 AM
    Monday, May 2, 2011 6:21 PM
    Answerer
  • Hi

     If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database. If the transaction log is growing too large, and the Log Reader Agent is running on a scheduled basis, consider shortening the interval between runs. Or, set it to run in continuous mode. If it is set to run in continuous mode (the default), ensure that it is running.

     

    Reference:BOL

    • Proposed as answer by Peja Tao Wednesday, May 4, 2011 8:44 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, May 10, 2011 1:51 AM
    Monday, May 2, 2011 9:11 PM
  • As mentioned in the two previous posts there is no concern buy your policy raises a question; If you already do take log backups then why not take them every 5 or 10 minutes?

    In addition since data is already replicated why not sched the distrib agent to run every 1 minute or 10 min etc ?

    Regarding the continouse mode my suggestion is that unless it is required by the business then better use a 1 min sched which less overloads and also is has a benefit for sync history and troubleshooting.

     

     


    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi
    Tuesday, May 3, 2011 8:20 AM