Post reply

Can not Kill SPID

  • October 25, 2010 at 2:55 am

    #143201

    Hi,

    We have an issue where a SPID is locking a table but when I run kill 51 the SPID does not clear. if I run KILL 51 with statusonly i get the following message.

    SPID 51: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

    I know most people will say wait as the rollback can take some time but the sql query has been rolling back since Thursday morning. So i belive there must be another issue because 4 days to roll back is not right.

    From what i've read the only solution seems to be restart the sql service but i do not want to do this if at all possible.

    So can any suggest ways I can clear this SPID?

    Thanks,

    Russell

  • October 25, 2010 at 3:17 am

    #1241459

    Try to check sys.dm_exec_requests !?

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • October 25, 2010 at 3:38 am

    #1241462

    Dugi (10/25/2010)

    Try to check sys.dm_exec_requests !?

    Yep, the process appears but not sure what the next steps should be.

    Any advice?

  • October 25, 2010 at 3:39 am

    #1241464

    russell.keeney (10/25/2010)

    Dugi (10/25/2010)

    Try to check sys.dm_exec_requests !?

    Yep, the process appears but not sure what the next steps should be.

    Any advice?

    Sorry I should have said that the blocking_session_id column is 0

  • October 25, 2010 at 4:39 am

    #1241477

    Wait or restart. There are no other options.

    If you restart, the rollback will continue during crash recovery. During this time the database will not be available (unless you have enterprise edition). It may clear quickly, it may take quite some time.

    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