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?