June 14, 2011 at 7:03 am
Our system admin has to reboot sql 2005 server and after the server came up on of the database (about 2tb sizsze) went into recovery mode and i dont see any any information in the sql server log, is there any other way to find when the dataabse would compeltely recover.
June 14, 2011 at 7:06 am
There should be messages in the log.
Check the database state (state_desc in sys.databases). What is it?
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
June 14, 2011 at 7:16 am
It says "RECOVERY" but i would like to know how much it recovered already
June 14, 2011 at 7:18 am
Are you sure there are no messages in 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
June 14, 2011 at 7:38 am
sure , no messages.
June 14, 2011 at 7:41 am
At the very least you ought to see something in the logs regarding starting the database. Is there anything in there mentioning that database?
SSMS -> Management -> SQL Server Logs -> Current
June 14, 2011 at 7:44 am
you can also check application eventviewer logs.
Regards,
MShenel
June 14, 2011 at 7:47 am
Post the latest error log (you can disguise the names of the DBs, they're not important)
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
June 14, 2011 at 7:58 am
Try to execute
SP_ResetStatus ---- to bring DB online
or
"Restore Database Database_name with Recovery" to bring the Database Online
June 14, 2011 at 8:01 am
this happened to me 2 weeks ago, you should see something in your error logs like
Message
Recovery of database 'x' (8) is 1% complete (approximately 7663 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
etc
etc
June 14, 2011 at 8:06 am
mohan.pariveda 18256 (6/14/2011)
Try to executeSP_ResetStatus ---- to bring DB online
or
"Restore Database Database_name with Recovery" to bring the Database Online
Those are for suspect or restoring databases, not recovering. Recovering is a process that has to complete and must be waited for. Nothing can stop a recovery and bring the DB online without it completing.
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
June 14, 2011 at 8:09 am
GilaMonster is right you have to wait till it completes.
Regards,
MShenel
June 14, 2011 at 8:29 am
Was the reboot in response to a problem or was it a clean shut down because of a service pack or something? If you shut down because of a hung process or something like that, you may be looking at a long recovery as transactions are rolled back or rolled forward.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 14, 2011 at 9:16 am
he last message i see for the database is "Starting database" when i have to start the machine manually as it was hung at the shutdown.
I used to see % compelte of recovery when eve this happened but not now which is weired.
I had to chek disk space for the log of this database , it is out of space and logfile does nothave enough space to recover, it would just hang there, wihtout giving any status, am i asusming correct?
June 14, 2011 at 9:40 am
So, do I understand correctly, the shutdown was taking a long time or was hung? Did you just power off? Sounds like it was trying to deal with an open transaction at that point. Now it's undoing that transaction (probably). I'd wait a while and see if you can recover. If you power off again, you might corrupt your database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply