Transactional replication question

• ALI SQLDBA

  SSCrazy

  Points: 2121

  More actions

  February 24, 2011 at 10:11 am

  #231729

  Hello Gurus,

  I will try to explain my problem as clearly as I can... please be patient until the end of the post.

  We are running a job to delete certain no.of rows in a table every day. we are facing blocking in our environment when this job runs. I have researched online about is_read_committed_snapshot_on. As per my understanding, if we change this from 0 to 1, row versioning will be in effect and will avoid locks in DB.

  Now, my question is, the server in the question is a publisher and a subscriber(different databases for pub and sub..transactional replication). Also, we have log shipping going on to a different server from this server.

  from your experience, can please you suggest me, if I change the is_read_committed_snapshot_on from 0 to 1, will this effect the high availabilty?

  please post a reply at your earliest convinience.

  Thanks

• M&M

  SSC-Insane

  Points: 21700

  More actions

  February 25, 2011 at 3:25 am

  #1291431

  I don't think it would impact high availability. I remember sometime last year I had faced this issue on one of the servers where transactional replication was configured. It didn't have an impact on changing the isolation level.

  M&M

• mazzz

  SSCertifiable

  Points: 6139

  More actions

  February 25, 2011 at 3:58 am

  #1291441

  Is the deletion job trying to delete a lot of records in one go, i.e. within one transaction? If so it would be worth deleting records in smaller batches

  ------------------------------------------------------------------------
  Bite-sized fiction (with added teeth) [/url]

• Carlton Leach

  SSCarpal Tunnel

  Points: 4202

  More actions

  February 25, 2011 at 4:15 am

  #1291446

  Instead of changing the isolation level: look at your delete code, in particular: execution plans.

  Does the delete query hit appropriate indexes?

  Are your statistics up to date?

  Carlton.