August 28, 2008 at 7:35 am
I have a database marked suspect.
I read the article here on how to recover a database in that mode, but I have had issues trying to follow the directions.
I brought up the server in single user mode
I ran sp_resetstatus on the database in question.
I tried to "use" the database but got the error "Database 'xxx' cannot be opened. It has been marked SUSPECT by recovery."
I ran the sp_resetstatus again but got the message "The suspect flag on the database "xxx" is already reset.
I tried to run DBCC CHECKDB 'xxx' from the master database but got the message "Incorrect syntax near 'xxx'."
What am I doing wrong?
Thanks!
Bill Mell
August 28, 2008 at 7:38 am
Try DBCC CHECKDB (YourDB)
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 28, 2008 at 7:44 am
I just tried that and got the error "Database 'xxx' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information."
August 28, 2008 at 7:51 am
What were the messages you were seeing in your error logs prior to running the sp_resetstatus?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 28, 2008 at 7:53 am
The error causing it to be marked suspect was:
File 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\xxx.mdf' appears to have been truncated by the operating system. Expected size is 21343424 KB but actual size is 21342688 KB
August 28, 2008 at 8:00 am
Next question, when / how did it become suspect. Was this during an instance startup or during an Attach database process. Please let me know.
Do you have a backup?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 28, 2008 at 8:06 am
I suspect it happened when the server lost power.
It is a database we are using for stress testing purposes, so there is no backup.
When I came in & checked the database in the morning I just saw that it was in a suspect state.
That's about all I know.
August 28, 2008 at 8:13 am
Did you stop / start the instance after you executed sp_resetstatus? Should have asked earlier. If so, then emergency mode would be the last option.
A couple of links -
http://www.nigelrivett.net/SQLAdmin/RecoverCorruptDatabase.html
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 28, 2008 at 8:15 am
Yes, I did stop & restart the database.
I will check those links.
Thanks,
Bill
August 28, 2008 at 8:52 am
An executive decision has been made to just discard the database, rather than to recover it.
August 28, 2008 at 8:58 am
Thanks for posting the outcome. Too bad you can't keep it around to try to work through some type of recovery. Good experience anyway.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply