0

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

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Paul
  • 23
  • 7
  • Have you ran the code from a different database? Also, make sure there is no active windows open that are pointing to the `master` database. – Eric S Nov 10 '16 at 16:16
  • Hi Eric, thanks, yes I did and then the error message was "Only user processes can be killed". Now, I am looking into how these connections are made by 'sa' and its around 23 and always active. – Paul Nov 14 '16 at 11:56

1 Answers1

0

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
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • Hi Ali, thanks, in that case you can kill all other database but not the Master database. My exact issue is: My Master database has 23 active connection via 'sa' login. I can't find out how these connection are active? or if I want to kill those, how to do this? As your suggested way you can't kill Master database connection. – Paul Nov 14 '16 at 11:05