Post reply

Transactional replication question

  • 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

  • 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

  • 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]

  • 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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply