Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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.

share|improve this answer
    
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. –  Johnny Barnes Sep 24 '13 at 13:51
    
There 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 Sep 24 '13 at 14:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.