How to mark a database as suspect

It might become necessary to drop a database, because it is unaccessible after loading a corrupt dump.

Please note that you should only follow these steps at your own risk, as you might loose the data in your database. Do not try this on critical databases.

The task can be done in two ways, manually, or via a stored procedure, which can be found here.

The manual way is as follows:
1. login as "sa".
2. Execute this command to find all databases already marked suspect:

 
SELECT name FROM master..sysdatabases WHERE STATUS & 320 = 320
go

3. If the database is already marked suspect, continue with step 5. If not then mark it as suspect:

sp_configure "allow updates", 1
go
USE master
go
begin transaction
UPDATE sysdatabases SET STATUS = 320
WHERE name = database_name
go
 

4. Verify that only one row was affected and then commit the transaction (or rollback if not):

commit transaction
go
 

5. Reset the allow updates option using sp_configure:

sp_configure "allow updates", 0
go

6. Shut down Adaptive Server with nowait:

shutdown WITH nowait
go

7. Restart the server to make the changes valid.
8. Now drop the database:

dbcc dbrepair(database_name,dropdb)
go
 

If the command produces an error, check if the database is dropped. It can happen that an error is raised although the database was successfully dropped. Contact Sybase Technical Support if you nevertheless run into other errors.

9. When the database is gone, re-create it and load it from an earlier dump.

How do I turn off marked suspect on my database?

If  one of your databases is marked suspect when ASE starts, you can follow the steps below to unmark it.

sp_configure "allow updates", 1
SELECT STATUS - 320 FROM sysdatabases WHERE dbid = db_id("dbname")
-- store this value for later reference
begin transaction
UPDATE sysdatabases SET STATUS = -32768 WHERE dbid = db_id("dbname")
commit transaction
shutdown
-- Restart the server
-- you'll now have to fix the problem that caused the database to be marked suspect in the first place. Then...
begin transaction
UPDATE sysdatabases SET STATUS = <value_stored_above> WHERE dbid = db_id("dbname")
commit transaction
sp_configure "allow updates", 0
reconfigure
shutdown
startserver -f RUN_*