Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

Question

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build).

Sometimes when I run my build I get this error:

ALTER DATABASE failed because a lock could not be placed on database 'MyDB'. Try again later.  
ALTER DATABASE statement failed.  
Cannot drop database "MyDB" because it is currently in use.  

I tried this:

ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

but I still cannot drop the database. (My guess is that most of the developers have dbo access.)

I can manually run SP_WHO and start killing connections, but I need an automatic way to do this in the auto build. (Though this time my connection is the only one on the db I am trying to drop.)

Is there a script that can drop my database regardless of who is connected?

Robert

Answer 1

Updated

For MS SQL Server 2012 and above

USE [master];

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('MyDB')

EXEC(@kill);

For MS SQL Server 2000, 2005, 2008

USE master;

DECLARE @kill varchar(8000); SET @kill = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'  
FROM master..sysprocesses  
WHERE dbid = db_id('MyDB')

EXEC(@kill); 

Threap

This is the better answer of the two; avoids taking the database offline and the accepted answer doesn't always work (sometimes it can't roll everything back). – Estabrook

Such a nice answer for aggregating kill statements all together. I would use a cursor to kill each process, which of course is not efficient at all. The technique used in this answer is brilliant. – Ice

I agree with Mark. This method should be the accepted answer as it is significantly more elegant and less impacting for the databases. – Circumspection

good one and quick. Only problem could be system spid so u can add WHERE dbid = db_id('My_db') and spid > 50 – Tipcat

How do you solve problem 'you can not kill your own process' ? I have to open a query window to execute new query. So I guess this is 'my own process', my own connection to the database. – Henhouse

@Henhouse You need to change the database context before run script. For ex.: USE [Master] – Threap

@SaurabhSinha - see sqlblog.com/blogs/adam_machanic/archive/2010/06/23/… - spid>50 not the best – Breadfruit

This was exactly what I needed, this is the better answer in my opinion – Jacobus

I used this code exactly, and I get error: "Incorrect syntax near 'Go'." Does anyone know why this would happen? – Eucalyptus

The database_id column is not available in SQL Server 2008. It's only available from SQL Server 2012 onwards. See MSDN for reference. – Skitter

The SQL 2000/2005 script doesn't work, as you cannot assign a value to a variable at the time of declaring it (that functionality was introduced in 2008). Changing the first line to DECLARE @kill varchar(8000) and adding SET @kill = '' works – Petcock

@FrenkyB: Add AND session_id!=@@SPID to the WHERE clause to not 'Kill' yourself... – Saffian

Had to restart all my Domains – Jube

Aggregate string concatenation (@kill = @kill + …) isn't supported and may yield unexpected results. It would be best to refactor using FOR XML PATH or, in SQL 2017 and later. STRING_AGG. – Confederation

I tried this and I get a "Only user processes can be killed." error message. what should I do? – Phylloxera

@BastienVandamme You can try WHERE dbid = db_id('My_db') and spid > 50 – Threap

I want to kill all connections except mine. Added and session_id != @@SPID to where clause. – Brainard

In my case, I'm using SQL Server 14.0 with Management Studio 17.9.1, and I have to say this is the only answer thats works to me. I was wanting to delete some databases wich accomplish a condition (as "name LIKE 'DBName_%'"), but those DB are all in use. Some services use them, so I was wanting to kill active connections first. Your answer helped me! Thanks! – Stephenson

Answer 2

USE master
GO
ALTER DATABASE database_name
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

Ref: http://msdn.microsoft.com/en-us/library/bb522682%28v=sql.105%29.aspx

Felicitation

Oddly enough it was USE master that was the key. I was trying to drop the db while connected to it (Duh!). Thanks! – Robert

If you use SET OFFLINE you have to manually delete the db files. – Oversweet

Wouldn't alter database YourDatabaseName set SINGLE_USER with rollback immediate be better? If you set it to OFFLINE (as @Oversweet states) the files will be left on disk, but with SINGLE_USER your connection will be left as the only one, and drop database YourDatabaseName will still remove the files. – Chadwick

@Chadwick in the script, you aren't connected to the DB, so it's not "your connection" but some other that will be left. Immediately after the set offline, you can issue set online to avoid the leftover files problem (yes, there's a race condition possibility). – Yvoneyvonne

Thanks! I didn't realize that some tab with sql statement in SQL Management Studio, executed earlier on this database, was causing my db to be reported in use. Use master, and go, made everything work! – Brookweed

@Chadwick Actually, even single user mode is buggy (that's really the only word for it): it ensures no other "normal" users can connect, but built-in processes such as those to auto-update statistics asynchronously are not disabled, and when they run, they'll highjack the single connection, killing your process. If there are any asynchronous processes that might run, there's no reliable way to enforce single user mode, alas - but set offline does work. I learned this the hard way on our CI, which used single user mode regularly as part of schema migration testing. – Collide

Answer 3

You can get the script that SSMS provides by doing the following:

1. Right-click on a database in SSMS and choose delete
2. In the dialog, check the checkbox for "Close existing connections."
3. Click the Script button at the top of the dialog.

The script will look something like this:

USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [YourDatabaseName]
GO

Headrace

I will not recommend taking databse in single user mode for any of users as this can cause you loose current connection to some application user and unnecesarry trouble to find those users and kill same or some times u have to restart sql server if connections to db are so frequent. – Tipcat

If you are using this command it means you are intentionally deleting the database, why does it matter then @SaurabhSinha? – Contaminant

Answer 4

Little known: the GO sql statement can take an integer for the number of times to repeat previous command.

So if you:

ALTER DATABASE [DATABASENAME] SET SINGLE_USER
GO

Then:

USE [DATABASENAME]
GO 2000

This will repeat the USE command 2000 times, force deadlock on all other connections, and take ownership of the single connection. (Giving your query window sole access to do as you wish.)

Errand

GO is not a TSQL command but a special command only recognized by the sqlcmd and osql utilities and SSMS. – Avent

Answer 5

To my experience, using SINGLE_USER helps most of the times, however, one should be careful: I have experienced occasions in which between the time I start the SINGLE_USER command and the time it is finished... apparently another 'user' had gotten the SINGLE_USER access, not me. If that happens, you're in for a tough job trying to get the access to the database back (in my case, it was a specific service running for a software with SQL databases that got hold of the SINGLE_USER access before I did). What I think should be the most reliable way (can't vouch for it, but it is what I will test in the days to come), is actually:
- stop services that may interfere with your access (if there are any)
- use the 'kill' script above to close all connections
- set the database to single_user immediately after that
- then do the restore

Insinuation

If the SINGLE_USER command is in the same batch as your (scripted) restore command -- not separated by a GO statement! -- then no other process can grab single user access, in my experience. However, I was caught tonight because my nightly scheduled job of set-single-user;restore;set-multi-user blew up. another process had exclusive file access to my bak file (smh) and therefore the restore failed, followed by the SET MULTI_USER failing ... meaning when I got called in the middle of the night to clean up the blood, someone else had SINGLE_USER access and had to be killed. – Millenarianism

Answer 6

Matthew's supremely efficient script updated to use the dm_exec_sessions DMV, replacing the deprecated sysprocesses system table:

USE [master];
GO

DECLARE @Kill VARCHAR(8000) = '';

SELECT
    @Kill = @Kill + 'kill ' + CONVERT(VARCHAR(5), session_id) + ';'
FROM
    sys.dm_exec_sessions
WHERE
    database_id = DB_ID('<YourDB>');

EXEC sys.sp_executesql @Kill;

Alternative using WHILE loop (if you want to process any other operations per execution):

USE [master];
GO

DECLARE @DatabaseID SMALLINT = DB_ID(N'<YourDB>');    
DECLARE @SQL NVARCHAR(10);

WHILE EXISTS ( SELECT
                1
               FROM
                sys.dm_exec_sessions
               WHERE
                database_id = @DatabaseID )    
    BEGIN;
        SET @SQL = (
                    SELECT TOP 1
                        N'kill ' + CAST(session_id AS NVARCHAR(5)) + ';'
                    FROM
                        sys.dm_exec_sessions
                    WHERE
                        database_id = @DatabaseID
                   );
        EXEC sys.sp_executesql @SQL;
    END;

Lattie

Answer 7

@AlexK wrote a great answer. I just want to add my two cents. The code below is entirely based on @AlexK's answer, the difference is that you can specify the user and a time since the last batch was executed (note that the code uses sys.dm_exec_sessions instead of master..sysprocess):

DECLARE @kill varchar(8000);
set @kill =''
select @kill = @kill + 'kill ' +  CONVERT(varchar(5), session_id) + ';' from sys.dm_exec_sessions 
where login_name = 'usrDBTest'
and datediff(hh,login_time,getdate()) > 1
--and session_id in (311,266)    
exec(@kill)

In this example only the process of the user usrDBTest which the last batch was executed more than 1 hour ago will be killed.

Cardoza

Answer 8

The accepted answer has the drawback that it doesn't take into consideration that a database can be locked by a connection that is executing a query that involves tables in a database other than the one connected to.

This can be the case if the server instance has more than one database and the query directly or indirectly (for example through synonyms) use tables in more than one database etc.

I therefore find that it sometimes is better to use syslockinfo to find the connections to kill.

My suggestion would therefore be to use the below variation of the accepted answer from AlexK:

USE [master];

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), req_spid) + ';'  
FROM master.dbo.syslockinfo
WHERE rsc_type = 2
AND rsc_dbid  = db_id('MyDB')

EXEC(@kill);

Preteritive

THIS! Although I personally use the sys.dm_tran_locks table as syslockinfo is marked obsolete, Also, you may want to exclude your current @@SPID just in case. – Farinose

Answer 9

You should be careful about exceptions during killing processes. So you may use this script:

USE master;
GO
 DECLARE @kill varchar(max) = '';
 SELECT @kill = @kill + 'BEGIN TRY KILL ' + CONVERT(varchar(5), spid) + ';' + ' END TRY BEGIN CATCH END CATCH ;' FROM master..sysprocesses 
EXEC (@kill)

Rondo

Answer 10

You can use Cursor like that:

USE master
GO

DECLARE @SQL AS VARCHAR(255)
DECLARE @SPID AS SMALLINT
DECLARE @Database AS VARCHAR(500)
SET @Database = 'AdventureWorks2016CTP3'

DECLARE Murderer CURSOR FOR
SELECT spid FROM sys.sysprocesses WHERE DB_NAME(dbid) = @Database

OPEN Murderer

FETCH NEXT FROM Murderer INTO @SPID
WHILE @@FETCH_STATUS = 0

    BEGIN
    SET @SQL = 'Kill ' + CAST(@SPID AS VARCHAR(10)) + ';'
    EXEC (@SQL)
    PRINT  ' Process ' + CAST(@SPID AS VARCHAR(10)) +' has been killed'
    FETCH NEXT FROM Murderer INTO @SPID
    END 

CLOSE Murderer
DEALLOCATE Murderer

I wrote about that in my blog here: http://www.pigeonsql.com/single-post/2016/12/13/Kill-all-connections-on-DB-by-Cursor

Fog

Answer 11

SELECT
    spid,
    sp.[status],
    loginame [Login],
    hostname, 
    blocked BlkBy,
    sd.name DBName, 
    cmd Command,
    cpu CPUTime,
    memusage Memory,
    physical_io DiskIO,
    lastwaittype LastWaitType,
    [program_name] ProgramName,
    last_batch LastBatch,
    login_time LoginTime,
    'kill ' + CAST(spid as varchar(10)) as 'Kill Command'
FROM master.dbo.sysprocesses sp 
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
WHERE sd.name NOT IN ('master', 'model', 'msdb') 
--AND sd.name = 'db_name' 
--AND hostname like 'hostname1%' 
--AND loginame like 'username1%'
ORDER BY spid

/* If a service connects continously. You can automatically execute kill process then run your script:
DECLARE @sqlcommand nvarchar (500)
SELECT @sqlcommand = 'kill ' + CAST(spid as varchar(10))
FROM master.dbo.sysprocesses sp 
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
WHERE sd.name NOT IN ('master', 'model', 'msdb') 
--AND sd.name = 'db_name' 
--AND hostname like 'hostname1%' 
--AND loginame like 'username1%'
--SELECT @sqlcommand
EXEC sp_executesql @sqlcommand
*/

Delvecchio

Answer 12

USE MASTER
GO
 
DECLARE @Spid INT
DECLARE @ExecSQL VARCHAR(255)
 
DECLARE KillCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT  DISTINCT SPID
FROM    MASTER..SysProcesses
WHERE   DBID = DB_ID('dbname')
 
OPEN    KillCursor
 
-- Grab the first SPID
FETCH   NEXT
FROM    KillCursor
INTO    @Spid
 
WHILE   @@FETCH_STATUS = 0
    BEGIN
        SET     @ExecSQL = 'KILL ' + CAST(@Spid AS VARCHAR(50))
 
        EXEC    (@ExecSQL)
 
        -- Pull the next SPID
        FETCH   NEXT 
        FROM    KillCursor 
        INTO    @Spid  
    END
 
CLOSE   KillCursor
 
DEALLOCATE  KillCursor

Pulido

Answer 13

If you want to only drop/delete a database you can select the option "Close existing connections" which is by default unset.

1. Right-click on Database catalog -> options.
2. Delete -> check option.
3. Ok

Virtuous

Answer 14

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'DatabaseName'
GO
USE [master]
GO
ALTER DATABASE [DatabaseName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

GO
USE [master]
GO
DROP DATABASE [DatabaseName]
GO

Durnan

Thank you for your interest in contributing to the Stack Overflow community. This question already has quite a few answers—including one that has been extensively validated by the community. Are you certain your approach hasn’t been given previously? If so, it would be useful to explain how your approach is different, under what circumstances your approach might be preferred, and/or why you think the previous answers aren’t sufficient. Can you kindly edit your answer to offer an explanation? – Brainwork

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Altazimuth

Answer 15

I have tested successfully with simple code below

USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Julianjuliana

I had issues with set SINGLE_USER when there already was a single active connection. – Yvoneyvonne