Sql-server – Should snapshot agent continue to run in transactional replication

snapshotsql serversql-server-2008-r2transactional-replication

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?

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

Related Question