SQL Server Performance

transactional replication

Discussion in 'SQL Server 2005 Replication' started by SQLDBAS, Apr 9, 2007.

  1. SQLDBAS New Member

    I have db setup with Transactional replication (PUSH)
    Can I set the Database in Simple recovery mode. ?
    Does it deletes the transaction which are required for replication ?before log reader read s them?

    Thanks
  2. satya Moderator

    No you cannot set it in simple recovery model, as transactional replication depends on transaction log ...
    The replication transactions will be handled by log reader agent as and when it is required or initiated, others will be flushed to disk when the checkpoint occurs.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  3. MohammedU New Member

    quote:Originally posted by SQLDBAS

    I have db setup with Transactional replication (PUSH)
    Can I set the Database in Simple recovery mode. ?
    Does it deletes the transaction which are required for replication ?before log reader read s them?

    Thanks


    Yes, I think you can set the database recovery model to simple when you configure transactional replication but it may effect your replication performance.
    No, Non-replicated transactions will not be removed from the log during checkpoint when the database is in simple recovery model.


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  4. SQLDBAS New Member

    Just want to share this .Even in simple recovery model ,transactional replication pushing Ttranactionsto subscriber ( all the data properly). I think we can set Simple recover model to database even it is setup as Transactional replication.
  5. MohammedU New Member

    As I mentioned simple recovery model is not a problem but sometimes it may effect replication peformance while reading non-replicated transactions from the log becuase log gets truncated when the checkpoint runs where as when the recovery model is full every time you run tlog backup...

    And also depends on the activity of the database...



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  6. satya Moderator

    Oh yes I was on for older SQl version, you can set it but it will invalidate the replication proces as it depends upon the transaction log records, due to the mismatch of LSn it will have downside.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page