Suspect Database 1

[optix]

I have a situation where a clients DB has been marked 'SUSPECT'. It appears that their (ex)DBA failed to backup the DB properly. Everything that I have tried has failed to bring back the DB. Here is the error:

Unable to find row in Sysindexes for clustered index on system catalog 1 in database 6. This index should exist in all databases. Run DBCC CHECKTABLE on Sysindexes in the database.
01/04/19 11:21:09.69 spid12 Unable to proceed with the recovery of dbid <6> because of previous errors. Continuing with the next database.

This is the interesting output from DBCC:

Checking 8
The total number of data pages in this table is 2.
The number of data pages in Sysindexes for this table was 35. It has been corrected to 2.
The number of rows in Sysindexes for this table was 1150. It has been corrected to 38.
*** NOTICE: Space used on the log segment is 0.00 Mbytes, 0.00.
*** NOTICE: Space free on the log segment is 99.84 Mbytes, 100.00.
Table has 38 data rows.
Checking 9

Does anyone have any ideas about how to get this thing back? Any help would be greatly appreciated!!!

Thanks
Troy Marshall

 

[foxbox]

from swynk.com/faq:

If you are not seeing any errors in the SQL: Server Errorlog or NT Event log, run an sp_reset_status to reset the flag. Stop and start SQL Server. If the database comes back up OK, run DBCCs against the database and perform an immediate backup if everything is clean. More than likely you will have some corruption in the database.

Try a dbcc on the database with no_checkpoint. I hope you have a backup. My experience is that, unless you get a clean dbcc of the database, it's hosed.

You can try to hack the status bit in sysdatabases. set it from whatever it is to 12. I've found this works if the database isn't too far gone.

** Back up master first. **

Get the database into &quot;Emergency&quot; mode by reseting the
suspect bit in sysdatabases (see &quot;Creating suplemental stored procedures&quot;, ch. 24, Admin Companion) and restarting SQL Server. Create another SQL Server on another machine and use SQL Transfer Manager (or Tools, Data/Object Transfer in Enterprise manager for 6.5) to get the data out of the damged database. Once the data is on a known good database (and has been backed up!), you can trash the original server and re-build it. If you use identical cpu types, character sets and sort orders for the two servers, a dump from one will load into the other.

If you can't reset the status, your database is gone beyond hope, You will have to restore it from the last good backup
br
Gerard
(-:

Better a known bug then a new release.

 

[optix]

I have tried to reset status and this does not work. When I bring the database back up it is marked suspect again. I am trying to get it up long enough to do a BCP or DUMP.

The error appears to be with sysindexes. I recieve an error:

Unable to find row in sysindexes for clustered indexes on system catalog 1 in database 6. This index should exist in all databases.

If my client had been performing backups I would have restored by now, but of course they haven't. If I can't get the DB back they will have to go back to last year!!! They went through just about everything you mentioned when they talked to Microsoft. I have tried setting sysdatabases.status to -32768 and then starting the from the command line in single user mode but then I cannot connect with ISQL or Enterprise Manager. I was hoping someone would know of some undocumented procedure that might help.
I certainly appreciate your feedback, if you have any more ideas I am willing to try anything!!!!!!!!!!!!

 

[foxbox]

this article:

http://support.microsoft.com/support/kb/articles/q65/0/57.asp

explains how you can update SYSINDEXES, but i suppose that was tried also together with M$ br
Gerard
(-:

Better a known bug then a new release.

 

[optix]

Thanks, this will help. The problem right now is that when I restart the database after changing the state it is marked suspect again.

Is there a way to start SQL without running the automatic recovery? I can't find an option for that!

Thanks!

Troy Marshall

 

[foxbox]

SQLSERVER with a -f command line option? That starts SQL Server with a minimum configuration.
?? br
Gerard
(-:

Better a known bug then a new release.

 

[aalmeida]

Have you look at the phisical db files? Some times the problem you having can be cause by those files been marked as Read Only. If that's the case just use the OS Command to change that. so the Server can have access to those files again. By the way this is a bug and MS has a fix for it on SP3.