MSSQL Killing database connection
There can be times that you need to kill all connections to a database so that you can perform emergency maintenance. Or for some other reason you need to disconnect the current people who are logged in.
So here is a little stored procedure that will do just that.
CREATE PROCEDURE [dbo].[KillConnectionsdb] @dbname varchar(MAX) AS DECLARE @spid int DECLARE @sql varchar(MAX) DECLARE cur CURSOR FOR SELECT spid FROM sys.sysprocesses P JOIN sys.sysdatabases D ON (D.dbid = P.dbid) WHERE LTRIM(RTRIM(D.Name)) = @dbname AND P.spid != @@SPID OPEN cur FETCH NEXT FROM cur INTO @spid WHILE @@FETCH_STATUS = 0 BEGIN PRINT CONVERT(varchar, @spid) SET @sql = 'KILL ' + RTRIM(@spid) PRINT @sql EXEC(@sql) FETCH NEXT FROM cur INTO @spid END CLOSE cur DEALLOCATE cur GO
It is really simply to use.
Just call the stored procedure with in the following way where 'Testing' happens to be the name of the database you want to kill off all the connections.
EXEC KillConnectionsdb @dbname = 'Testing'
Note: this stored procedure will avoid attempting to kill its own connection in the process.
Did You find this page useful?
Thanks for the feeback. Please consider sharing with others.