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

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
*/

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

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:<br>
- stop services that may interfere with your access (if there are any)<br>
- use the 'kill' script above to close all connections<br>
- set the database to single_user immediately after that<br>
- then do the restore

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

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

[![enter image description here][1]][1]


[1]: