Skip to content

Instantly share code, notes, and snippets.

@ronascentes
Created January 11, 2020 21:42
Show Gist options
  • Save ronascentes/2fd1165ac8f5d4f0414fb5061a92f158 to your computer and use it in GitHub Desktop.
Save ronascentes/2fd1165ac8f5d4f0414fb5061a92f158 to your computer and use it in GitHub Desktop.
Kill all the current connections of a SQL instance
DECLARE @session_id INT, @sql NVARCHAR (4000);
DECLARE database_curs CURSOR FAST_FORWARD FOR
SELECT c.session_id
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE c.session_id <> @@SPID AND s.is_user_process = 1;
OPEN database_curs;
FETCH NEXT FROM database_curs INTO @session_id;
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @sql = N'KILL ' + CAST(@session_id AS nvarchar(6));
EXEC sp_executesql @sql;
PRINT N'Session ID ' + CAST(@session_id AS nvarchar(6)) + N' was killed.';
END;
FETCH NEXT FROM database_curs INTO @session_id;
END;
CLOSE database_curs;
DEALLOCATE database_curs;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment