January 20, 2010 at 2:03 pm
Hi,
I cancelled a long running update statement that was running against a large table and now sql server is rolling back changes. The database state is "recovering" and I need to turn on the application asap. Is there a way to cancel this?
Thanks,
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
January 20, 2010 at 2:07 pm
The only way to stop it from continuing is to shut down SQL Server. I would strongly advise against this. The recovery is performing the necessary steps to ensure data integrity so it will continue that when you start it back up and remain in its current state until rollback is complete.
Can't you shut down the application without touching the DB?
January 20, 2010 at 2:13 pm
If the database state is recovering then it means someone has already tried to cancel the rollback by restarting SQL server and found out that it didn't exactly help.
You have to wait it out. There is no way to cancel the database restart-recovery, it has to finish or the database would be transactionally (and possibly structurally) inconsistent and would have to be marked suspect.
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
January 20, 2010 at 2:14 pm
John,
The application is off and you're right because I restarted the service and when it came back online the state is now "recovering". The thing is the application needs to be back online by tomorrow, AM.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
January 20, 2010 at 2:17 pm
Gail,
Thanks.
So you will advise to leave the application offline until the the database is fully recover, correct?
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
January 20, 2010 at 2:18 pm
Like Gail said, you'll have to wait it out.
Gail,
I too was wondering if the instance was already restarted, but not sure if recovery was being confused with ROLLBACK status for the spid.
January 20, 2010 at 2:19 pm
Yes the instance was restarted, sorry I wasn't clear about that.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
January 20, 2010 at 2:29 pm
cos_ta393 (1/20/2010)
Yes the instance was restarted, sorry I wasn't clear about that.
People often do that without realising what they are doing.
An update (insert, delete) runs for too long. Someone panics, perhaps because it's affecting other users, hits the stop button.
The query doesn't stop, just goes into rollback. Any locks take are still held. Person who panicked and stopped the query panics more and decides to restart SQL.
SQL restarts, detects that the database in question was not cleanly shut does and starts recovering it. Entire database is now offline and will stay that way until restart-recovery is finished.
There is one more way to make things worse, I've seen someone do it once and it resulted in the complete loss of the entire database.
General rule: Wait for queries to finish. If you decide to rollback be aware that rollback usually takes longer than the query had to that point. Don't restart SQL, it only makes matters worse.
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
January 20, 2010 at 2:32 pm
cos_ta393 (1/20/2010)
The thing is the application needs to be back online by tomorrow, AM.
You might want to have a word with your boss. The restart recovery will take as long as it takes, there is no way to cancel it, no way to make it speed up. You can check the error log for the estimated time remaining, but be aware it usually underestimates.
If the database is still recovering tomorrow AM, the application can't come online, no matter how important it is that it do so.
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
January 20, 2010 at 2:40 pm
Great lesson learned...
Thanks Gail, John. Your inputs are very much appreciated.
Thanks,
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
September 19, 2017 at 11:13 am
some remarks:
- if you catch it early enough you are better off killing it
- it seems to be doing it even in case it is performing just a sSELECT witount any modification of the database. Microsoft should be able to something about this.
- if i can't stop it I would like to know how long it is still going to last - is there a way to find out ?
- in a complex query, is there a way rto find out where it ran away ?
September 19, 2017 at 11:55 am
found out soumething meanwhile
- look in Activity Monitor if you find something like 'preemptive wait' your process is waiting for some OS operation to complete. This may last for ever unless you find (in task manager) the process responsible for the wait and kill it. This helps immediately.
September 19, 2017 at 12:02 pm
Please note: 7 year old thread.
A SELECT without modifications doesn't have to roll back, as there's nothing to undo. It won't stop instantly, but it doesn't rollback. If, however, the select is in a transaction along with some data modifications, they have to roll back.
Run KILL <session id>, if it's already rolling back, you'll get a % complete. Database recovery % complete and estimated times are written into the error log
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply