We have transactional replication running for a large number of publications on sql server 2008 R2 2 node active/active cluster. I have noticed that the snapshot agent job runs hourly and it looks like it does a refresh of the publications (literally, a new snapshot?). During this time we experience locking issues with the blocker being this job. Should this be doing a refresh this often if no new articles are being added or changed?
Add a comment
|
1 Answer
I have noticed that the snapshot agent job runs hourly and it looks like it does a refresh of the publications (literally, a new snapshot?) Should this be doing a refresh this often if no new articles are being added or changed?
NO. Snapshot should not be run frequently, unless you are adding/dropping articles.
Also, Transactional replication uses Concurrent Snapshot processing; meaning it does not hold share locks in place during the entire snapshot generation, thereby allowing users to continue working uninterrupted while replication creates initial snapshot files.
The sync_method parameter governs which mode is used - native, character, concurrent or concurrent_c.
answered Sep 20, 2013 at 20:46
-
Is there a parameter that would force a refresh of the snapshot to be accomplished? I have looked at the properties and profile of the snapshot agent and job and can't figure out why I'm getting the bcp of the articles every time it runs (hourly). The only option I see I have is to change the schedule of the job so it runs less frequently or disable the schedule altogether. Commented Sep 24, 2013 at 13:51
-
1There should be no schedule attached to snapshot agent. Just disable the schedule. When you want to run a snapshot, then the Replication monitor will call the snapshot job - which is a sql agent job.– Kin ShahCommented Sep 24, 2013 at 14:18