Suspect DB

145 pts.
Tags:
SQL Server 2000
SQL Server security
Suspect database
Hi, I am using sql server 2000 and recently found 2 of the DBs are marked as Suspect and I don't have access to them any more.Would you please help me with this? Thanks, Ellie

Answer Wiki

Thanks. We'll let you know when a new response is added.

The first thing to do will be to find out why they went suspect. Check your ERRORLOG for information.

It could be that the disk didn’t respond correctly when the machine was rebooted and now it’s working fine, or it could be that the disk has failed completly.

Once you have resolved the initial issue depending on the problem you can flip the databases back into normal mode. This is done by changing the value of the status column in the sysdatabases table to 24. It’s probably at some really high number at the moment. This should give you access back to the databases. If they go back into suspect mode then either the disk problem hasn’t been resolved, the database files aren’t where SQL expects them to be, or the database has been damaged and needs to be restored from the last good backup.

<pre>exec sp_configure ‘allow updates’, 1
reconfigure with override

update master.dbo.sysdatabases
set status = 24
where name = ‘<i>YourDatabaseName</i>’

exec sp_configure ‘allow updates’, 0
reconfigure with override</pre>

You may need to restart the SQL Server instance after changing the status column for SQL Server to see the change.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following