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?
Who is Participating?
menreeqConnect With a Mentor Author Commented:
We solved the issue by moving the DB from 2012 to 2008, once we were replicating from 2008 to 2008 it worked like a champ.  Thanks for the help.
Transactoinal replication in SQL Server is pretty clever and since 2008 (or perhaps even 2005) reliability and resiliency has been improved significantly.

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.
lcohanDatabase AnalystCommented:
" 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 ..."
menreeqAuthor Commented:
Figured it out
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.