Dropping a Database When DROP DATABASE Fails

You can use the following procedure to drop a database if DROP DATABASE fails.

Caution Do not use this procedure unless directed to do so by your primary support provider or this manual, or unless there is no critical data in the database. Otherwise, you might damage your database.

    To drop a database when DROP DATABASE fails

1. Log in as sa.
2. Check to be sure that the database has been marked suspect:

   select name from master..sysdatabases
   where status & 256 = 256

3. If the database is marked suspect, go to step 4. If it has not been marked suspect, mark the database suspect using either of the following methods:
   • Execute the sp_marksuspect stored procedure described in "Resetting the Suspect Status," later in this chapter.

   Or

   • Update the sysdatabases table manually as shown in the following example:

     sp_configure 'allow updates', 1
     go
     
     reconfigure with override
     go
     
     use master
     go
     
     begin tran
     update sysdatabases set status = status | 256
     where name = 'database_name'
     go
     

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

     commit tran
     

     Then reset the allow updates option using sp_configure:

     sp_configure 'allow updates', 0
     go
     
     reconfigure with override
     go
     

     Finally, restart SQL Server to initialize the change.

4. Remove the database using DBCC DBREPAIR:

   dbcc dbrepair(database_name,dropdb)

   Note DBCC DBREPAIR sometimes displays an error message even though it successfully drops the database. If an error message appears, verify that the database has been removed by executing USE database_name. This should fail with a 911 error because you dropped the database. If any other error occurs, contact your primary support provider.