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
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.
- Right-click on the database and select "Delete" (or left-click it and press the 'del' key).
- When the 'Delete Object' dialog appears, make sure to checked "Close existing connections" (see below, it's unchecked by default).
- 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.....
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.
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.