after the system hand-up and reboot my database got suspect help pls

-- Anonymous, April 17, 2001

Answers

hkwkh,

From SQL Server Books Online:

What causes a suspect database? How can I fix this?

A database can become suspect if one of these conditions is true:

If one or more database files are not available. If the entire database is not available. If one or more database files are corrupted. If a database resource is being held by the operating system. To resolve a suspect database:

Check the SQL Server error log and resolve all problems. Reset the suspect status by executing sp_resetstatus. For more information, see Resetting the Suspect Status and Troubleshooting Recovery.

The following is provided by Neil Pike:

In addition to these ideas, also check out support.microsoft.com/support for the Microsoft Knowledgebase. Specifically Q165918.

Firstly look in \LOG and look at all recent errorlog(s). There WILL be an indication here as to why the database has been marked suspect. You need to fix whatever the problem is first (i.e. missing file, permissions problem, hardware error etc.)

Then, when the problem has been fixed and you're either sure that the data is going to be ok, or you have no backup anyway, so you've nothing to lose, then change the database status to normal and restart SQL Server. To change the database status, and to get more information on recovery, look up the sp_resetstatus sp in the Books Online.

If you don't have access to sp_resetstatus information, then the short version of this is :-

UPDATE master..sysdatabases SET status = status ^ 256 WHERE name =

If the database still goes back into suspect mode, and you can't fix the original problem, and you have no recent backup, then you can get information out of the database by putting it into emergency mode. If you do this, extract the data/objects out with bcp/transfer manager and then rebuild the database. Note that the data may be corrupt or transactionally inconsistent.

Issue the following command to put the database into emergency mode (you'll need to allow updates first)

UPDATE master..sysdatabases SET status=-32768 WHERE name=''

Hope this helps,

Eric

-- Anonymous, April 18, 2001