How to Drop a Database When drop database Fails

Follow the steps in this section to drop a database when drop database fails. Do not use these steps unless directed to do so by this book, or unless there is no critical data in the database.

1.Log in as the “sa”.

2.Check to make sure the database has been marked “suspect.” The following query produces a list of all databases which are marked suspect:

1> select name from master..sysdatabases

2> where status &256 = 256

3> go

3.If the database is marked “suspect”, go to step 4. If it is not marked “suspect”, mark it in one of the following ways:

a.Execute the sp_marksuspect stored procedure discussed under “How to Mark a Database “suspect””, and restart Adaptive Server to initialize the change.

b.Use the procedure below:

1> sp_configure”allow updates”, 1

2> go

1> use master

2> go

1> begin transaction

2> update sysdatabasesset status = 256

3> where name = “database_name”

4> go

Verify that only one row was affected and commit the transaction:

1> commit transaction

2> go

Reset the allow updates option of sp_configure:

1> sp_configure “allow updates”, 0

2>go

Restart Adaptive Server to initialize the change.

4.Remove the database:

1> dbccdbrepair(database_name,dropdb)

2> go

dbcc dbrepair sometimes displays an error message even though it successfully drops the database. If an error message occurs, verify that the database is gone by executing the use database_name command. This command should fail with a  911 error, since you dropped the database. If you find any other error, contact Sybase Technical Support.