SQL 7 DB in suspect state

[Edgar Zapata]

We have a Microsoft SQL Server 7.00 - 7.00.699 (Intel X86)
One of its Databases is in (suspect9 state.
How can I get it back to life?

Thank you.
regards.

[Igor Karasik]

Edgar,
SQL7, antique...
You need to find cause of the problem, check sql logs and event viewer for any errors/warning.
You can reset the suspect status using "sp_resetstatus".
Syntax: sp_resetstatus database_name
Restart server after running sp_resetstatus.
Run DBCC Checkdb to check DB.
(you can also use DBCC CheckCatalog and
DBCC Checkalloc to identify the bad page in the database)
Regards, Igor

[Edgar Zapata]

Igor,
What are the potential risks that may cause the execution of the sp_resetstatus?
Any ideas?
Thank you.

I just want to make sure before I run it.

Thank you.

[Igor Karasik · Accepted solution]

Edgar, IMHO there are no risk when you run
sp_resetstatus, anyway you DB in suspect status now.
Do you have recent DB backup?

[Edgar Zapata]

I know now why you mentioned backup.

I got this result after tunning the sp_resetstatus:

Result:
Prior to updating sysdatabases entry for database 'SUMMARYDB', mode = 0 and status = 264 (status suspect_bit = 256).
For row in sysdatabases for database 'SUMMARYDB', the status bit 256 was forced off and mode was forced to 0.
Warning: You must recover this database prior to access.


I'll see if we have a recent backup.

regards.

[Igor Karasik]

Edgar, did you restart MSSQL server after sp_resetstatus?

Even if it doesn't work you can try following:
-Set the database property 'allow updates':
exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
-Set the database to 'Emergency Mode':
UPDATE master..sysdatabases SET status=-32768 WHERE name='MyDBName'
-Rebuild the database log file:
dbcc rebuild_log ('myDBName','path to my log.LDF')
Warning: you will lost Transactional consistency after this operation
-Run DBCC CHECKDB
-Now you can execute sql statement vs your database and you can use DTS Wizard to export tables to another database.
And it always good to have recent DB backup.

[Edgar Zapata]

We did not have a good DB backup.
We finally had to create a new DB from scratch.
Tnks.