Kill database connections

Kill database connections script. Usefull before db restore.

Kill all connections to database

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); 

Kill all connections apart form yours

DECLARE @Table TABLE(
    SPID INT,
    ECID INT,
    Status VARCHAR(MAX),
    LoginName VARCHAR(MAX),
    HostName VARCHAR(MAX),
    BlkBy VARCHAR(MAX),
    DBName VARCHAR(MAX),
    CMD VARCHAR(MAX),
    REQUESTID INT
)

INSERT INTO @Table EXEC sp_who

SELECT  *
FROM    @Table

select @@SPID

SELECT  SPID
FROM    @Table
where hostname = 'DESKTOP-N16OGI7' and spid <> @@SPID

declare users cursor for
SELECT  SPID
FROM    @Table
where hostname = 'DESKTOP-N16OGI7' and spid <> @@SPID

declare @userSpid int
DECLARE @kill varchar(8000); SET @kill = '';
open users
fetch users into @userSpid
while @@fetch_status=0
begin
    SET @kill = 'kill '+ CONVERT(varchar(5), @userSpid) + ';'
    print @kill
    EXEC(@kill);
    fetch users into @userSpid
end

close users
deallocate users

To see the dirty data that is not yet commited

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Further reading
Script to kill all connections to a database
KILL (Transact-SQL)
@@SPID (Transact-SQL)
Rollback: What happens when you KILL a session?
How to kill or rollback active transaction?
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)