Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106

    Unanswered: killing a thread

    Hey all,

    Just wondering if there is any way to kill a thread within an sqlerver process. The thread we are trying to kill is a rollback statement that has been running for a very long time.

    Any ideas ?


    Thanks in advance,
    Kilka

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    C4 or nitroglycerin ?

    I can't think of any reason to ever kill a rollback, other than to shutdown the server so that recovery can do the rollback faster when it has exclusive use of the database. Anything that prevents a rollback from completing essentially permanently corrupts the database.

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Is it a result of a previous kill?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    yeah. It's the result of a previous kill.

    Currently, I get this error message when I try and kill the spid.

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

    The rollback has been running for a couple hours now. Does anyone know if there is any way to see what it is rolling back ?

    I'm going to bounce the server and see what happens.

  5. #5
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    ok, so it appears the rollback was just hanging there. A bounce was all that was required. I'm still not sure if it's possible to tell what the rollback is working on. I think it would usefull to know. The reason I ask is because the spid is the result of another app working on the surface and it's quite difficult to tell what the app was doing at the time....

    Cheers,
    -Kilka

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You sure you're not Darkwing Duck?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The rollback in 9 out of 10 will take longer (in many cases much longer) than the original transaction. Rollback is impossible to kill with a KILL command. The only way you can undo what KILL does is by bouncing the server, which you've already done. When the database gets recovered during the recovery process, the original rollback attempts to dismiss any originating transaction (basically ignoring the altered data pages recorded in the transaction log) and just moves on with what was actually committed and recorded in the trx log..
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jul 2002
    Posts
    229
    Maybe there's some undocumented way of doing it? I'm in the situation right now that I'm waiting for a rollback that will take hours, and I just want to re-create the database from an old backup anyway. However, there are production databases on the same server so I can't shut down anything outside the particular database.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •