locked
can not drop database..... RRS feed

  • Question

  • TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Drop failed for Database ' DBNAME'.  (Microsoft.SqlServer.Smo)


    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Cannot drop database " DBNAME" because it is currently in use. (Microsoft SQL Server, Error: 3702)

    BUTTONS:

    OK
    ------------------------------


    Harry
    Tuesday, August 30, 2011 7:01 PM

Answers

  • Harry,

    The error is saying that the database is currently being used. Personally I like to open up a new command window and issue an alter atabase to put it in single user mode then use that same connection to drop the database.

    USE MASTER
    GO
    
    ALTER DATABASE {DB} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    GO
    
    DROP DATABASE {DB}
    GO
    

    The WITH ROLLBACK IMMEDIATE tells SQL Server to rollback any uncommited transactions and terminate all connections, allowing only 1 (single user).

    -Sean

    • Marked as answer by Harry SQL DBA Wednesday, August 31, 2011 12:21 AM
    Tuesday, August 30, 2011 7:14 PM
    Answerer

All replies

  • Msg 911, Level 16, State 1, Line 1

    Could not locate entry in sysdatabases for database 'DBNAME'. No entry found with that name. Make sure that the name is entered correctly.


    Harry
    • Merged by David Dye Tuesday, August 30, 2011 7:26 PM This thread was started after original and this provides greater detail, but is for the same issue
    Tuesday, August 30, 2011 6:22 PM
  • It would appear that the database you are trying to use does not exist.  If you are using something like

    USE DBNAME;
    

    then the database specified is not on the server you are querying.  Change DBNAME to the name of the database that is on the instance of SQL and that should resolve the issue.  If you can provide more information, the query or activity that is creating this error, than a more directed answer or assistance can be provided.

     

    Hope this helps

     


    David Dye http://sqlsafety.blogspot.com/
    Tuesday, August 30, 2011 6:39 PM
  • The same database is there on the same server....i m just dragging it from object explorer.....


    Harry
    Tuesday, August 30, 2011 6:45 PM
  • I checked it.....  .ndf file is diferent when I checked the properties of database and also it is different when i manually go to that path....I am confused....how to change it...
    Harry
    Tuesday, August 30, 2011 6:58 PM
  • Harry,

    The error is saying that the database is currently being used. Personally I like to open up a new command window and issue an alter atabase to put it in single user mode then use that same connection to drop the database.

    USE MASTER
    GO
    
    ALTER DATABASE {DB} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    GO
    
    DROP DATABASE {DB}
    GO
    

    The WITH ROLLBACK IMMEDIATE tells SQL Server to rollback any uncommited transactions and terminate all connections, allowing only 1 (single user).

    -Sean

    • Marked as answer by Harry SQL DBA Wednesday, August 31, 2011 12:21 AM
    Tuesday, August 30, 2011 7:14 PM
    Answerer
  • Can you please provide a query or action that you are performing that is causing the action?

     


    David Dye http://sqlsafety.blogspot.com/
    Tuesday, August 30, 2011 7:16 PM
  • Hi,

    Finally got rid of this issue....actually 2 of the users were connected to the database.....but the above given "single user mode" query did not work...so I had to find the processes running from sysprocesses system tables....and then I had to manually kill those processes....and then finally I got rid of this issue....Thanks for your support.....


    Harry
    Wednesday, August 31, 2011 12:20 AM