how do you fix databases that are in suspect mode.

We have a SQL server 2000 database that is in suspect mode. how do I recover from this.

Steve SamsonAsked:
Who is Participating?
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Look at the posting:

When did it get suspect, after a restart? Do you have a antivirus program on the server, it might hog the physical files, and hence the server can not access them. Stop and start the SQL Services to find out.

Check the log for errors, is it possible that your harddrive is failing. If so you better find out fast.

Can you provide more info on when it got suspected, if you tried a SQL server restart. Is it the only database on the sql. What SP is your SQL running, on what OS?

Hemantgiri S. GoswamiModerator - MSDN ForumsCommented:
I would recommend to follow the instruction given here to recover the suspected database
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Good article ghemant

For the author ccutler:
two things to emphasize
1. If you need to use the 'repair_allow_data_loss' switch with DBCC. Maybe it's a better approach (assuming the db is in full recovery mode) to  make a tail log backup, and then simply drop and restore the database the normal way. If it's not in full recovery mode but in simple, check your SLA. You'll most likely find that a data loss of transactions up to 24h is acceptable (maybe the application can rerun those by design on appserver or clients). It should be evaluated if it's better to know that all the data is correct, but transactions for this day needs to be done all over.
2. If DBCC finds that your database files has been damaged, check the log for errors regarding to disk failure, typically 823, 824 and 825 (825 is a warning, the other are failures). These indicate that there is something wrong with the I/O subsystem. You need to find out why corruption occurred, it will most likely occur again so preventive actions needs to be taken.

Steve SamsonAuthor Commented:
Worked like a charm.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.