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
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.
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.
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.
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.
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.
