Post reply

rrebuilding indexes by one connection and select or update statements by other connection on the same database work?

  • July 23, 2011 at 11:33 pm

    #244841

    hi,

    I had check db integrity followed by rebuilding indexes job scheduled and at the same time a SPID(connection) is performing update or select operations on that database. what is the solution for the job to succeed other than killing that SPID ?

  • July 24, 2011 at 1:00 am

    #1357507

    It create a dead lock between re-index table and update or select table.

    but you can reduce the load to change the DB to simple recorvery mode first and after re-index change back to Full recovery mode.

    Do not forget to take the full backup after you change the recovery mode.

  • July 24, 2011 at 3:41 am

    #1357518

    Mathew M.Varghese (7/24/2011)

    It create a dead lock between re-index table and update or select table.

    Blocking, yes. Deadlocks, it shouldn't

    but you can reduce the load to change the DB to simple recorvery mode first and after re-index change back to Full recovery mode.

    Terrible advice. There's no good reason to switch to simple, break the log chain and leave yourself at risk of dataloss. If the log growth is a problem (nothing to do with locking) then bulk-logged recovery can be used for the duration

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 25, 2011 at 5:42 am

    #1357697

    Hi GilaMonster,

    If using enterprise edition it's possible to rebuild indexes online without bloking the access to that index right?

    Best regards,

  • July 25, 2011 at 7:40 am

    #1357768

    Yes. It's mostly online, not fully online, but it will mostly keep the index available.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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