November 16, 2011 at 4:29 pm
Good evening, i need your help please!!
i dont know very much about Recovery issue deeply and im not a DBA but ive got 2 questions.
what is the reason when in a production system all sqlserver processes are running perfectly, but suddenly the system crashed or colapse down and the sqlserver runnig process failed and the database turned into a recovery states that took almost 3 hours.
1.I suppose when you stop the instance (stop the engine and the agent)even when are running processes could cause the recovery. doesnt it ? is there any other reason?
2. when you kill a sesion with the command kill number_id and that sesion starts rolling back and the rollback is in progress monitoring the percentaje,that rolling back could cause the recovery? (for my humble opinion i dont think so)
the above questios is because i was asked to kill a sesion number that was taking almost 12 hours running and acording to the DBA diagnostics is that the Database "stuck" making a recovery identifyng the sesion i killed4
i'll appreciate your help, thanks
November 16, 2011 at 4:48 pm
First, is this SQL 2000?
Second, what do you mean a recovery state or recovery process? Any query could potentially block other work and make the server appear to be unresponsive.
If you kill a connection, the work done by the connection must be rolled back in order to maintain ACID principles and data integrity. If you end up with a large number of work done on a large table (update every row, delete every row, etc), then that must be undone. If you do updates for an hour, it might take an hour, or more, to roll back.
If you better explain what happened, we might be able to help.
November 16, 2011 at 5:16 pm
The reason is most likely available in your error logs if you are talking about a database suddenly showing up in the "recovering" state.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 16, 2011 at 5:38 pm
thanks, well the system is sqlserver 2000
first, when monitoring active sessions when sp_who active was running ok, but suddenly i type that command once again and didnt respond and last show me a error message likely a broken connenction.
and in the event viewer logs appeared and error message referencing
and i was asked to cancel an id processes it started rolling back
According what ive described before, somebodys is allegdely acusing me for crashing the system for 2 reasons.
1st evidence: to kill a sesion that i was asked to kill for my superior that took a long time - (as you explained to me, rollback just undoes the changes) but i dont think it crashes the system.
secondly, i have some charges that i crsahed the system, but theres no evidence for doing something wrong im my side, neiher i restart the system , nor anything weard, or if i mistaked (human error) ill accept it, but dindnt ,just kill the session i was asked to kill.
November 17, 2011 at 1:26 am
pavargasq (11/16/2011)
1.I suppose when you stop the instance (stop the engine and the agent)even when are running processes could cause the recovery. doesnt it ? is there any other reason?
Running or rolling back.
2. when you kill a sesion with the command kill number_id and that sesion starts rolling back and the rollback is in progress monitoring the percentaje,that rolling back could cause the recovery?
No, not unless the SQL instance is restarted while that is rolling back.
Recovery is the process of bringing a database online after a restart, an attach, opening a closed database or bringing online one that's offline.
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
November 25, 2011 at 10:25 am
Would you have any details from your error_log prior to after the events described ?
In my practice, I have seen cases where someone, trying to be helpful, is attempting to "kill all user connections" to a given database, by using the detach db task in E/M, but completes the overall task inadvertingly.
Obvious, now the "detached db" would need to be re-attached.
Check windows security log to see if there are any notable successful logins from non-dba's during that time period. Might be a help with server access you migth never expect.
Just a thought to review.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply