topic Re: SQL 7 DB in suspect state in BackOffice Products

SQL 7 DB in suspect state

Edgar Zapata — Mon, 02 Jan 2006 07:59:52 GMT

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.

Re: SQL 7 DB in suspect state

Igor Karasik — Mon, 02 Jan 2006 09:25:22 GMT

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

Re: SQL 7 DB in suspect state

Edgar Zapata — Mon, 02 Jan 2006 09:47:41 GMT

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.

Re: SQL 7 DB in suspect state

Igor Karasik — Mon, 02 Jan 2006 10:57:35 GMT

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

Re: SQL 7 DB in suspect state

Edgar Zapata — Tue, 03 Jan 2006 04:53:14 GMT

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.

Re: SQL 7 DB in suspect state

Igor Karasik — Tue, 03 Jan 2006 08:18:14 GMT

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.

Re: SQL 7 DB in suspect state

Edgar Zapata — Wed, 15 Mar 2006 03:21:47 GMT

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