menreeq
asked on
SQL replication locking
We have transactional replication in place that only replicates changes and not entire sets of tables every time. Furthermore, we are specifically pushing data from the Publisher every 1 hour. My subscriber seems to be holding onto tables, please see attached, and as a result – SQL job drags out a long time (it reads from replicated tables and does data tabulation.) All documentation on this states that replication doesn’t lock replication tables, however, it seems to be creating connections to updates and not closing those efficiently. We are publishing from 2012 and to 2008 subscriber over a WAN.
I cannot find any concrete information of replication blocks, so I’m thinking it is something that is manageable/configurable. In attached png my process is 60 and process 62 suspended it. I read about transactional replication and that Snapshot Agent only locks tables on initial snapshot replication, not any concurrent ones. Should this be happening?
Should I put in place an implementation of read committed isolation that uses row versioning when the READ_COMMITTED_SNAPSHOT database option is ON? Or do Snapshot that is enabled when the ALLOW_SNAPSHOT_ISOLATION database option is ON?
rep.png
I cannot find any concrete information of replication blocks, so I’m thinking it is something that is manageable/configurable. In attached png my process is 60 and process 62 suspended it. I read about transactional replication and that Snapshot Agent only locks tables on initial snapshot replication, not any concurrent ones. Should this be happening?
Should I put in place an implementation of read committed isolation that uses row versioning when the READ_COMMITTED_SNAPSHOT database option is ON? Or do Snapshot that is enabled when the ALLOW_SNAPSHOT_ISOLATION database option is ON?
rep.png
" My subscriber seems to be holding onto tables, "
You should check Locks/Blocks on the subscriber side and I recommend using SQL own Performance Dashboard for that or sp_locks on the Subscriber SQL.
The fact that you replicate over a WAN can also be the reason why "SQL job drags out a long time ..."
You should check Locks/Blocks on the subscriber side and I recommend using SQL own Performance Dashboard for that or sp_locks on the Subscriber SQL.
The fact that you replicate over a WAN can also be the reason why "SQL job drags out a long time ..."
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Figured it out
Based on what you've described, I'd suggest you to check to see if there is a heavy job, such as a bulky report, ETL operation or perhaps maintenance plan (index rebuild, backup, database integrity check etc.) which might be clashing with your replication.
Having said that I've had no problem with replication going on simultaneously with the maintenance jobs, but in my case replications where continuous not scheduled. Is there a reason why you don't allow continuous replication instead of every hour? If you let the transactions replicate in smaller chunks the probability of the above mentioned clashes will be much lower.