Question

Suspect DB

SQL Server security, SQL Server 2000, 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

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.

exec sp_configure 'allow updates', 1
 reconfigure with override
 
 update master.dbo.sysdatabases
       set status = 24
 where name = 'YourDatabaseName'
 
 exec sp_configure 'allow updates', 0
 reconfigure with override

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