December 27, 2011 at 12:48 pm
Hey all,
As a database administrator working with databases in full recovery mode, I often run into issues with runaway transaction log growth. This is especially frustrating because, at the company I work for, we don't use point-in-time restoration, so as far as I can tell, the transaction logs are kept around for nothing. In the event of an emergency, we restore from a daily full backup, and this is an acceptable data loss risk. I want to suggest switching our databases to simple recovery mode.
However, there's one thing I want to figure out before doing this--will changing the restore model have any implications for transactional replication. According to everything I've read, the answer is no. But I just wanted to make sure this is the case. I have distribution agents skipping certain errors. Will these transactions not be retained under the simple recovery mode? As I understand it, since they have not been committed, they will not be marked as inactive in the transaction log.
We have some substantial problems with replication due to intermittent connectivity issues and the like. Does anyone know any reasons to avoid using the simple recovery mode in these instances, or is it OK? I'd appreciate any advice anyone can give.
December 27, 2011 at 1:21 pm
There is no impact to any form of replication by changing the recovery models of the database to any of the options. The two features are independent.
http://sqlinthewild.co.za/index.php/2008/12/05/a-new-sql-myth/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 27, 2011 at 5:04 pm
Thanks so much for your reply. It is particularly reassuring that I'm not the only one who was worried about this. 🙂 Thanks again.
December 28, 2011 at 1:12 am
For a little more detail on what happens under the covers, you may want to read these:
http://qa.sqlservercentral.com/articles/Administration/75461/
http://qa.sqlservercentral.com/articles/Transaction+Log/72488/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply