How to kill open processes in MS SQL Server
You may frequently need in especially development and test environments instead of the production environments to kill all the open connections to a specific database in order to process maintenance task over the SQL Server database.
In such situations when you need to kill or close all the active or open connections to the SQL Server database, you may manage this task by using the Microsoft SQL Server Management Studio or by running T-SQL commands or codes.
Killing processes with MSSQL Server Management Studio
If you open the SQL Server Management Studio and connect to a SQL Server instance you will see the Activity Monitor object in the Object Explorer screen of the related database instance. You can double click the Activity Monitor object or right click to view the context menu and then select a desired item to display the activities to be monitored on the Activity Monitor screen.
As seen on below you can monitor and view process id’s and process details on the list of prcesses running on the database instance. If you want you can filter processes based on specific values like user, database or status.
Note that default view when displayed the screen is first opened is filtered only for non-system processes which means system processes which own the first 50 reserved processid’s are not listed in the view by default. You can view system processes by removing the filter on “Show System Processes” criteria in the filter settings screen.
You can kill a process by a right click on the process in the grid and selecting the Kill Process menu item. You will be asked for a confirmation to kill the related process and then will kill the open connection to the database over this process.
Killing processes with T-SQL
By using t-sql commands or sql codes, similarly closing connections can be implemented by a few methods. One of the methods is using a cursor which loops for all the active connections of the related database and kill these processes. This method was also mention on SQL Server article named How to Alter a SQL Server Database as Single User Mode and as Multi User Mode
The below code block can be used to kill all the processes which are connected to the database named @DatabaseName except the process that the code block is running in the scope of. You can also set the database name by the DB_NAME() property.
DECLARE @DatabaseName nvarchar(50) DECLARE @SPId int SET @DatabaseName = N'Works' --SET @DatabaseName = DB_NAME() DECLARE my_cursor CURSOR FAST_FORWARD FOR SELECT SPId FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId OPEN my_cursor FETCH NEXT FROM my_cursor INTO @SPId WHILE @@FETCH_STATUS = 0 BEGIN KILL @SPId FETCH NEXT FROM my_cursor INTO @SPId END CLOSE my_cursor DEALLOCATE my_cursor
A second way to drop the active connections of a database can be implemented by generating dynamic sql commands that runs a list of “Kill @spId” commands.
DECLARE @DatabaseName nvarchar(50) SET @DatabaseName = N'Works' --SET @DatabaseName = DB_NAME() DECLARE @SQL varchar(max) SET @SQL = '' SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId -- SELECT @SQL EXEC(@SQL)
A very similar to the code above an other code block can be used by using the COALESCE as shown below
DECLARE @DatabaseName nvarchar(50) SET @DatabaseName = N'Works' DECLARE @SQL varchar(max) SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId --SELECT @SQL EXEC(@SQL)
The queries can be modified for specific needs, for example you may create a stored procedure that drops all existing active connections. You may pass database name or database id as parameter or use the current database information to kill processes except its own process, etc.