Question :
I have a couple transactional replication publications that make large changes (nearly 1 billion records) to a publication article. These transactions are captured by the log reader and stored in the distribution database for up to a day before we run the distribution agents to the two subscribers.
In conjunction with these large transactional replication publications, we also have several smaller publications that run daily and sometimes multiple times per day. What we are seeing is that while the log reader is reading the large transactions and storing them in the distribution database, the smaller, daily publications are being blocked until the log reader completes the write to the distribution database. Note: these publications use the same distribution database on a single distributor server.
For both scenarios above, the distribution agents are not scheduled but are run on-demand: an SSIS package calls several CMD distrib.exe while passing the parameters for each publication/subscription combination. There has not been an issue executing these agents.
Since these are two entirely different sets of data, although publishing from the same database, I am considering changing the transaction isolation level of the database to SNAPSHOT to avoid locks and allow concurrent processing of the transactions in the distribution database.
Does anybody have experience doing this? I’ve used SNAPSHOT isolation level for another database where we were having deadlocks while accessing the same table but different records and it worked out fine.
If changing the isolation level isn’t the way to go, how can I reduce the impact of this large log reader write to the distribution database?
Answer :
With 1 billion changes, I would be worried about the size of TempDB when the row version is added to everything. Hilary Cotter responded to this same question here – https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2edbdc37-c2ed-4c84-ae72-16f89404172b/sql-server-replication-and-enabling-readcommittedsnapshot-on-the-distributor?forum=sqlreplication.
He is definitely the man you want to ask replication related questions to.
If you have 1 billion changes, have you looked into replicate the execution of a stored proc?
Replicate execution of stored procedure will save yourself a lot of time and space and reduce the exact scenario you are hitting, where distribution db is filled with commands. If this is some kind of batch process, then marking the stored proc as replicated, it will log the stored proc call only, and replicate just the call to the subscriber.