SQL Server : kill active connections hold by MASTER database

Question

I have tried below code to kill SQL connection other than Master database:

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

This code worked fine for me. But when I have run below query to see more active connections then I found my MASTER database has 21 active connection.

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

while I wanted to close all active connection with MASTER database then it failed and error message was: "Cannot use KILL to kill your own process."

Please let me know, how can I kill all 21 active connection from MASTER database which is holding by 'sa' account ?

Many thanks for your kind support.

/Paul

Answer 1

You can kill all active connection to your database by putting your database in the single user mode, I usually do this when I am trying to restore over an existing inuse database. Something like ....

USE master;
GO

ALTER DATABASE [DB_Name]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

USE [DB_Name]  --<-- Grab that single available conncection  
GO

USE master;     --<-- Now no more connections to your database Tadaaaaa... 
GO