Killing all connections in SQL Server is something that you may need to do from time to time. The two times that I’ve needed to do this most is when I’m restoring a database in my test environment or when I need to rename a database. Both of these operations require that there are not any open connections to the SQL Server database. There are a couple good scripts that I will show here. My favored method is to set it in single user mode.
Kill All Connections Using Single User Mode
This is the easiest way kill all the connections to the database. It basically sets the database to only allow 1 user (you) and it will kill all the other connections.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- Kill all connections and set to single user mode USE MASTER GO ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO -- Do your operations -- Set the database back in to multiple user mode USE MASTER GO ALTER DATABASE [DatabaseName] SET MULTI_USER GO |
Kill All Connections Using The Kill Command
The following query will loop through all the open processes on the database and kill each one. Note that this may take a little bit of time to execute if there are long running transactions, it may take some time to roll them back.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
USE MASTER GO DECLARE @Spid INT DECLARE @ExecSQL VARCHAR(255) DECLARE KillCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT DISTINCT SPID FROM MASTER..SysProcesses WHERE DBID = DB_ID('DatabaseName') OPEN KillCursor -- Grab the first SPID FETCH NEXT FROM KillCursor INTO @Spid WHILE @@FETCH_STATUS = 0 BEGIN SET @ExecSQL = 'KILL ' + CAST(@Spid AS VARCHAR(50)) EXEC (@ExecSQL) -- Pull the next SPID FETCH NEXT FROM KillCursor INTO @Spid END CLOSE KillCursor DEALLOCATE KillCursor |
Reference: https://msdn.microsoft.com/en-us/library/bb522682(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/ms173730.aspx