Recovering a suspect database

Question

Recovering a suspect database

MyatSuu

SSC Veteran

Points: 292
More actions
January 12, 2004 at 2:23 am

#107695

Help!
One of my databases is suspect now. I don't want to lose today transactions. Would you pls tell me how I solve this problem! Thanks a lot. []

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ritch Ten Centuries Points: 1044 More actions · Answer 1

Hi,

If you haven't a recent backup then you can do the following. Look in your logs first, to see if is autorecovering. If nothing is in the logs, run sp_who2 to see if there is a rollback on the db in question. If there is leave it it will 'hopefully' recover.

If not, and as reset status will not work you'll have to set the db to emergency mode update sysdatabases set status = 32768

Then set the db to single user

exec sp_dboption 'yourdb', 'single user', 'true'

Then run dbcc checkdb ('yourdb', REPAIR_REBUILD)

Set single user to false.

Hopefully this will work, it has for me a few times. Good luck!! Ritch

P.S Don't reboot the server or restart SQL!!

"I didn't do anything it just got complicated" - M Edwards

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

You also need to be sure that you know why the database went suspect. Check the error log for a reason.

You can clear the suspect flag as well with

sp_resetstatus <db name>

Ritch Ten Centuries Points: 1044 More actions · Answer 3

Hi Steve, sp_resetstatus hasn't always worked for me. Had several occassions where I ran sp_resetstatus on a suspect db, which it said was successful. Then tried to set the db to single user but it failed saying the db couldn't be accessed. The db was then marked as suspect again. But when I set the status manually to emergency it worked fine. I could never find an explaination, any ideas why? Cheers Ritch

"I didn't do anything it just got complicated" - M Edwards

gljjr SSChampion Points: 10080 More actions · Answer 4

I would highly suggest you call Product Support Services to resolve this.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 5

I would agree with above. The suspect flag is just a bit setting in sysdatabases. If it keeps getting reset, then likely the issue has not been resolved and PSS is your best bet at resolving this.

Ritch Ten Centuries Points: 1044 More actions · Answer 6

Thanks, the problem was with my old employer. So guess I'll never know

"I didn't do anything it just got complicated" - M Edwards

cpo360 SSC Enthusiast Points: 148 More actions · Answer 7

sp_resetstatus requires you to restart the SQL Server service, was that done when it didn't seem to work for you?

Ritch Ten Centuries Points: 1044 More actions · Answer 8

Yep, I tried it with restarting and not restarting.

"I didn't do anything it just got complicated" - M Edwards