March 27, 2011 at 10:20 pm
Hi
I got a doubt in Transactional replication .
Wil it supports for simple recovery model databases?
My doubt is transactions are truncated automatically in simple recovery when check point occurs,then how sync will happen in transactional replication.
Please help me .
March 28, 2011 at 1:19 am
Yes, you can use the Simple recovery model... the transaction log is only truncated after a checkpoint AND all transaction marked for replication have been processed.
March 28, 2011 at 1:39 am
Thank You very much..
Am i right in this..
"transactions are replicated before check point occurs in transactional replication "
March 28, 2011 at 2:35 am
gvram6 (3/28/2011)
"transactions are replicated before check point occurs in transactional replication "
I don't really understand that statememt... where did you see it?
The checkpoint process has nothing to do with replication. It writes changed pages from memory to disc.
The only tie in with replication is with truncation of the transaction log. In simple recovery, this normally happens after a checkpoint, but if there are replicated transactions in the log, truncation of the log has to wait until they have been processed as well.
March 28, 2011 at 2:40 am
Not necessarily. They could be sent before, they could be sent after, it really doesn't matter.
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
March 28, 2011 at 2:59 am
Got Cleared ...now..
Thanks alot..
June 15, 2013 at 1:49 am
is there transaction log in simple recovery model
June 15, 2013 at 2:10 am
Of course.
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
June 15, 2013 at 9:50 am
manikanta1207 (6/15/2013)
is there transaction log in simple recovery model
Yes. There has to be a transaction log, because if uncommitted changes have been written to disc and the system breaks it has to be able to undo those changes when it comes up again - and roll-back of uncommitted transactions is carried out based on the uncommitted changes recorded in the transaction log. Also, when the system breaks there may be a transaction that has been committed and some of whose updates are recorded only in RAM store and in the transaction log (because a checkpoint hasn't yet occurred since that transaction was committed) - and recovery when the system comes back up has to ensure that those updates are applied to the database records, so it has to retrieve those updates from the transaction log.
Tom
June 16, 2013 at 11:25 pm
thankyou
August 18, 2014 at 12:26 am
As far as Replication is Concerned , There is no effect Of Recovery Model of the database in Updating the logged transactions back to the subscribers through Distributor .
In Simple Recovery Model Check Point Only clears the committed transactions from Log file and writes the pages back to disk , But in case Of Replication - changes made to the Published articles in the publisher will maintain a mark like 'marked for replication ' in the Log file . These Marked log records will not be truncated through checkpoint , Only cleared after they are updated to Distributor/Subscribers through Log reader Agent .
Hence , we can observe the Log file growth if there is any disturbance at distributor End in executing the Log reader Agent job .But you should be careful in case Of BULK OPERATIONS as they are minimally logged to log file.
August 18, 2014 at 2:35 am
chandumca54 (8/18/2014)
But you should be careful in case Of BULK OPERATIONS as they are minimally logged to log file.
Not when there's transactional replication.
p.s. 3 year old thread.
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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply