not - sql server drop database with files




unable to drop and create database in sql server (8)

I'm working with SQL Server 2008 and I can't seem to do drop and create a database.

I've tried a few different ways but I always end up failing to drop or trying to "use" before it seems to be created.

My current attempt looks like this.

use master;
GO
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'test')
BEGIN
DROP DATABASE [test];
END
GO
CREATE DATABASE [test];
GO
use [test];
GO

The GO were suggested on a MS forum as a way to stop some issues that occur when selecting databases.

With this I currently get the output (with a ore existing database of the same name) of:

Msg 3702, Level 16, State 4, Line 3
Cannot drop database "test" because it is currently in use.
Msg 1801, Level 16, State 3, Line 1
Database 'test' already exists. Choose a different database name.
Msg 2714, Level 16, State 6, Line 2
There is already an object named 'staff_type' in the database.

With the last 2 lines repeated for every table in my database.


  1. Right-click on the database and select "Delete" (or left-click it and press the 'del' key).
  2. When the 'Delete Object' dialog appears, make sure to checked "Close existing connections" (see below, it's unchecked by default).
  3. Press "OK".


Along with mr_eclair's answer above, I would like to add:

  • All Query window must be closed where the currect db is selected.
  • Another option is make the db in single user mode.>> it will kill all the other users processes
  • set OFFLINE WITH ROLLBACK IMMEDIATE. it will make the db in offline mode and bring it back
  • use sp_who2 to know the users using the current db. and killthe required spids

I know I´m late to the game. But here is how I do this in one step. This was happening so often I did´t want to do this in many steps so I combined it to one single step.

DECLARE @databaseName VARCHAR(30); 
DECLARE @resource_type_to_kill VARCHAR(30); 
DECLARE @processIdToKill INT;

SET @databaseName = 'yourDatabaseName' 
SET @resource_type_to_kill = 'DATABASE'

DECLARE @TempSession TABLE
(
    ProcessIdToKill INT,
    DatabaseName VARCHAR(100),
    Request_Mode VARCHAR(100),
    HostName VARCHAR(100),
    LoginTime VARCHAR(100),
    LoginName VARCHAR(100),
    Status VARCHAR(100),
    Reads VARCHAR(100),
    Writes VARCHAR(100)
);
INSERT @TempSession
SELECT DISTINCT
    session_id,
    name,
    request_mode, 
    host_name,
    login_time,
    login_name,
    status,
    reads,
    writes
FROM    sys.dm_exec_sessions
    LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id =  sys.dm_tran_locks.request_session_id
    INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE   resource_type = @resource_type_to_kill
AND name = @databaseName
ORDER BY name

--SELECT * FROM @TempSession --Debugging

SELECT @processIdToKill = ProcessIdToKill FROM @TempSession

--SELECT @processIdToKill --Debugging

--Run kill for the process that is using the database to be dropped.
DECLARE @SQL nvarchar(1000)
SET @SQL = 'KILL ' + CAST(@processIdToKill as varchar(4))
PRINT 'Killing the process'
EXEC (@SQL)

--And then drop the database
DECLARE @DropSQL nvarchar(1000)
SET @DropSQL = 'DROP DATABASE ' + @databaseName
PRINT 'Dropping the database'
EXEC (@DropSQL)

If there are many processes that are using the database you´ll just have to run this multiple times.


If you are getting the above error while using Master. then you need to close SQL Server Management Studio completely and again open it and connect to it and run your above query.....

Hope,it'll works.....


This will give you all the current connections:

select spid, hostname, [program_name], open_tran, hostprocess, cmd
from master.dbo.sysprocesses 
where dbid = db_id('your_database_name')

Then you could use a t-sql cursor to execute kill @spid, where the value for @spid is from the previous query.


Totally random thought here. But if you have a SQL DB project open in Visual Studio, its open-ness will occupy processes even if you aren't taking any actions or have open query windows in SSMS.

This was the issue in my case. Closing Visual Studio completely, allowed me to drop the database with no issue.


You need to close all the query window using this database also you might need to restart the SQL Server completely. This might solve your problem.


try this:

use master;
GO

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO
.....

This will rollback any transaction which is running on that database and brings SQL Server database in a single user mode.





tsql