This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing scripts to kill all your running processes or sessions of SQL Server.
DBA always set time of database maintenance activities, but sometimes they find few running sessions so they require closing all connections of a SQL Server Database.
Before executing this script, please take care and verify all running connections and processes otherwise this script will harm to your data.
You require this type of script very occasionally, but I am sharing here because this is also one of the important scripts for SQL DBA.
How to find running sessions or processes in SQL Server?
Command to Kill particular session_id:
|
1 |
KILL 'session_id' |
Script to kill multiple sessions:
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @DatabaseName nvarchar(50) SET @DatabaseName = 'dbrnd' DECLARE @SQL varchar(max) SET @SQL = '' SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId EXEC(@SQL) |
Change your database mode from multi-user to single-user:
Once you change the database mode from multi-user to single-user, SQL Server immediately stops all the connections of the database. No one can connect to that database.
The DBA can perform the maintenance activity and after that, they can again change the database mode from single-user to multi-user.
To set Single User:
To set Multi User:
