locked
SSMS - cannot drop database - in use RRS feed

  • Question

  • I'm new to SQL Server. I just install SQL Server 2008 on my home computer.  I loaded the sample AdventureWorksDW database. I close SSMS and opened it again, then tried the loaded script again and get the message:

    Msg 3702, Level 16, State 4, Line 2

    Cannot drop database "AdventureWorksDW" because it is currently in use.

    I'm the only one using the computer. How do I know the database is in use and how do I close it?

    Thank you,
    sboxtops
    sboxtops@aol.com

    Wednesday, June 17, 2009 3:59 PM

Answers

  • SSMS will keep a foot on the database and will prevent you to drop it if you are currently with the object navigator on the database :-). Move to another database and right click on the database to delete, select drop and you should be fine. If you have any other processes accessing the database beside you, use the

    USE Master
    ALTER DATABASE SomeDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE  SomeDatabase

    to force the disconnects of current sessions and initiate the deletion.

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Wednesday, June 17, 2009 6:36 PM
  • If you still find any issue while setting it single user mode....

    Use the below mentioned command will drop all existing connection to your Database.

    USE master
    go

    DECLARE @dbname sysname

    SET @dbname = 'PUT YOUR DATABASE NAME HERE'
    DECLARE @spid int
    SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
    WHILE @spid IS NOT NULL
    BEGIN
    EXECUTE ('KILL ' + @spid)
    SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
    go
    DROP DATABASE  @dbname
    END
    go

    I hope it would help.

    BR
    PRAVEEN BARATH
    Thursday, June 18, 2009 1:05 PM
  • Hi,

    Supplementing Jens K. Suessmeyer's solution, make sure you have the "Close existing connections " option selected when you right click and select delete for the target database.

    Also, an easier way to looking the current connections is to right click on the server instance node (the root node from the object explorer pane) and selecting ActivityMonitor .


    Regards,

    Syed Mehroz Alam
    My Blog | My Articles
    Thursday, June 18, 2009 7:59 AM

All replies

  • Hi,

    can you paste the loaded script.?

    Also when you drop any database use master something like this:-

    Use Master
    drop database database_name
    GO

    and press F5(Execute) and your database will be drop.


    Thanks


    regards,
    Kumar
    Wednesday, June 17, 2009 4:40 PM
  • SSMS will keep a foot on the database and will prevent you to drop it if you are currently with the object navigator on the database :-). Move to another database and right click on the database to delete, select drop and you should be fine. If you have any other processes accessing the database beside you, use the

    USE Master
    ALTER DATABASE SomeDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE  SomeDatabase

    to force the disconnects of current sessions and initiate the deletion.

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Wednesday, June 17, 2009 6:36 PM
  • Hi,

    Supplementing Jens K. Suessmeyer's solution, make sure you have the "Close existing connections " option selected when you right click and select delete for the target database.

    Also, an easier way to looking the current connections is to right click on the server instance node (the root node from the object explorer pane) and selecting ActivityMonitor .


    Regards,

    Syed Mehroz Alam
    My Blog | My Articles
    Thursday, June 18, 2009 7:59 AM
  • If you still find any issue while setting it single user mode....

    Use the below mentioned command will drop all existing connection to your Database.

    USE master
    go

    DECLARE @dbname sysname

    SET @dbname = 'PUT YOUR DATABASE NAME HERE'
    DECLARE @spid int
    SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
    WHILE @spid IS NOT NULL
    BEGIN
    EXECUTE ('KILL ' + @spid)
    SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
    go
    DROP DATABASE  @dbname
    END
    go

    I hope it would help.

    BR
    PRAVEEN BARATH
    Thursday, June 18, 2009 1:05 PM
  • USE Master
    ALTER DATABASE myDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE  myDatabase

    Just want to report that this script did the job for me. I was experimenting with Importing an Access 2016 database into SQL Server 2019 and could not use the "Delete" command, always ran into the same error message, that the database was in use.

    Thank you so much for the help!

    Sunday, March 15, 2020 6:20 PM