Post reply

Rollback/suspended

  • August 4, 2011 at 3:52 pm

    #241706

    The update query on around 2000000 records table is killed and now it is in rollback and suspended state from 2 hours can we restart sql service to stop it.

    Regards,
    Shivrudra W

  • August 4, 2011 at 4:06 pm

    #1364033

    You can, but it will continue the rollback after the server restart, with your database offline and unavailable (unless you're using enterprise edition, in which case the database will be online, but all the resources locked)

    A rollback takes as long or longer than the operation itself did. If the rollback is suspended it means it's waiting for some resource, there should be a wait_type and wait_resource. Check what they are.

    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

  • August 4, 2011 at 4:06 pm

    #1364034

    such a command could easily take a while to rollback.

    rerun the kill spid command again with the statusonly option to see how far it has got.

    i.e.

    kill spid with statusonly

    ---------------------------------------------------------------------

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

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