SQL Server database "SUSPECT"

[mjlee]

Hi,
I've encountered a problem that my SQL Server database has been marked
"Suspect" and it didn't allow me to view it .
Can Anyone help me to fix this emergency problem?
Thak you very much!

Bibo
bi...@nccu.edu.tw

[Neil Pike]

Bibo,

> I've encountered a problem that my SQL Server database has been marked
> "Suspect" and it didn't allow me to view it .
> Can Anyone help me to fix this emergency problem?
> Thak you very much!

Q. My SQL Server database has been marked "suspect" - what can I do?
(v1.4 12.1.1999)

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

Firstly look in <sql>\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 = <dbname>

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)

For SQL 6.5 and below

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

For SQL 7.0

UPDATE master..sysdatabases SET status=32768 WHERE name='<dbname>'

Neil Pike MVP/MCSE. Protech Computing Ltd
(Please post ALL replies to the newsgroup only unless indicated otherwise)
For SQL FAQ entries see www.ntfaq.com/sql.html
and http://www.swynk.com/faq/sql/sqlserverfaq.asp
and GO MSSQL lib 1 on Compuserve

[bi...@alchemysolutions.com]

Try books online look up sp_resetstatus

:>

Bill Weiss

On Mon, 11 Jan 1999 11:18:00 +0800, "mjlee" <mj...@ms1.url.com.tw>
wrote:

>Hi,

>I've encountered a problem that my SQL Server database has been marked
>"Suspect" and it didn't allow me to view it .
>Can Anyone help me to fix this emergency problem?
>Thak you very much!
>

>Bibo
>bi...@nccu.edu.tw
>
>