January 5, 2012 at 3:00 pm
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
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
Nothing helpful. Estimated rollback completion 0%. Estimated time remaining 0 seconds.
January 6, 2012 at 5:09 pm
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.
January 6, 2012 at 6:56 pm
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
are you still seeing same?
January 7, 2012 at 12:46 pm
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
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
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
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