Post reply

KILLED/ROLLBACK Query

  • January 5, 2012 at 3:00 pm

    #250862

    A coworker was creating a clustered index on a table with about 90MM rows, then decided to kill his query after about 6 hours. 6 days later, sys.dm_exec_requests is listing the command as still being in KILLED/ROLLBACK mode. This is pretty strange, IMO, as it seems like it shouldn't need to take this long to rollback an index, let alone one that was only running for 6 hours. Sorry for the vague details, but does anyone have any suggestions? If you need any information, let me know.

  • January 6, 2012 at 2:23 am

    #1429484

    What information do you have when you run

    KILL your_spid WITH STATUSONLY;

    GO

    ?

    You should have something like this :

    spid 54: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.

  • January 6, 2012 at 8:16 am

    #1429653

    Nothing helpful. Estimated rollback completion 0%. Estimated time remaining 0 seconds.

  • January 6, 2012 at 5:09 pm

    #1429911

    Hopefully this question is resolved by now, but just in case (and for future reference) I've found the percent complete column of Adam Mechanic's sp_whoisactive to be extremely helpful in monitoring things like rollbacks and backups.


    Colleen M. Morrow
    Cleveland DBA

  • January 6, 2012 at 6:56 pm

    #1429920

    Hi

    In my perspective roll backs may take 75% more time(If you kill after 1 hr, it will take at least 1hr 45mins to roll back), to end the transaction. i will suggest to use proper query hints and indexes will doing some complex inserts.

    Caution: Do not restart the SQL server services.

  • January 7, 2012 at 11:37 am

    #1430050

    are you still seeing same?

  • January 7, 2012 at 12:46 pm

    #1430065

    Yes, it is still in killed/rollback. It's been like this for 7 days now, and it only ran for 6 hours to start with. From what I can see, I don't have a choice but to restart sql services.

  • January 7, 2012 at 2:31 pm

    #1430073

    old hand

    do not restart the SQL services, it will be much worse cause database may turn into recovery mode.

    thats dangerous move.

  • January 7, 2012 at 2:35 pm

    #1430074

    yeah, this is my last resort. i've read a couple of articles that say that when a dbcc operation is canceled it can sometimes get to a point where it will never rollback and the only resort is to restart sql services. at this point, there's really nothing else i can do. it's been running for over a week now.

  • January 7, 2012 at 4:53 pm

    #1430084

    HI

    If you think the DB is not much in use by other processes, go head for it.

Viewing 10 posts - 1 through 9 (of 9 total)

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