Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
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);
How do you kill all current connections to a SQL Server 2005 database?
The reason that the approach that Adam suggested won't work is that during the time that you are looping over the active connections new one can be established, and you'll miss those. You could instead use the following approach which does not have this drawback:
-- set your current connection to use master otherwise you might get an error
use master
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--do you stuff here
ALTER DATABASE YourDatabase SET MULTI_USER
How to close existing connections to a DB
This should disconnect everyone else, and leave you as the only user:
alter database YourDb set single_user with rollback immediate
Note: Don't forget
alter database YourDb set MULTI_USER
after you're done!
How to close all existing connections to a DB programmatically
You get that error when you are call Open()
on a connection twice. You should make all SqlConnection objects you create inside using
blocks and only open them once.
If you are reusing connections "to make it faster" .NET already does that by default for you via Connection Pooling but you must dispose of the connection object to make it work.
Automatically kill session of some specific task
Sometimes I use this old query to get rid of sessions with specific description:
declare @t table (sesid int)
--Here we put all sessionid's with specific description into temp table
insert into @t
select spid
from sys.sysprocesses
where dbid = db_id()
and spid <> @@SPID
and blocked <> 0
and lastwaittype LIKE 'LCK%'
DECLARE @n int,
@i int= 0,
@s int,
@kill nvarchar(20)= 'kill ',
@sql nvarchar (255)
SELECT @n = COUNT(*) FROM @t
--Here we execute `kill` for every sessionid from @t in while loop
WHILE @i < @n
BEGIN
SELECT TOP 1 @s = sesid from @t
SET @sql = @kill + cast(@s as nvarchar(10))
--select @sql
EXECUTE sp_executesql @sql
delete from @t where sesid = @s
SET @i = @i + 1
END
How do I specify close existing connections in sql script
You can disconnect everyone and roll back their transactions with:
alter database [MyDatbase] set single_user with rollback immediate
After that, you can safely drop the database :)
Related Topics
Best Way to Do Nested Case Statement Logic in SQL Server
How to Add 'On Delete Cascade' in Alter Table Statement
How to Get a SQL Row_Number Equivalent for a Spark Rdd
Window Functions or Common Table Expressions: Count Previous Rows Within Range
Differencebetween SQL, Pl-SQL and T-Sql
SQL Server Select Where Any Column Contains 'X'
How to Convert Postgresql 9.4's JSONb Type to Float
Sqlite Equivalent of Row_Number() Over (Partition by ...)
Improve SQL Server Query Performance on Large Tables
Finding the Next Available Id in MySQL
Why Can't You Mix Aggregate Values and Non-Aggregate Values in a Single Select
SQL Server 2008 - Help Writing Simple Insert Trigger
Postgresql - SQL - Count of 'True' Values
Modify Default Value in SQL Server
Why Are Logical Reads for Windowed Aggregate Functions So High