Skip to Content
SAP Adaptive Server Enterprise

How to drop a suspected database if drop database fails ?

Tags:

When drop database fails, Please follow the below steps to drop the database.

1. Log in with a user having "sa_role".

2. 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 where status & 320 = 320

2> go

3. If the database is marked "suspect", go to step 4.

If it is NOT marked "suspect", Please follow the steps below :

3.1

1> use master

2> go

1> sp_configure "allow updates", 1

2> go

1> begin transaction

2> update sysdatabases set status = 320 where name = database_name

3> go

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

1> commit transaction

2> go

1> sp_configure "allow updates", 0

2> go

3.2.  Shut down Adaptive Server with nowait:

1> shutdown with nowait

2> go

3.3. Restart the ASE server.

4. Remove the database:

1> dbcc dbrepair(database_name,dropdb)

2> go

5. Verify that the database has been removed by executing the use database_name command.