Will "the Mighty" Strohl

HOW TO: Kill Processes (Sessions) on SQL Server Using T-SQL

If you are anything like me, you always want to find the most efficient way to do something.  For instance, you cannot restore a database without first stopping all of the processes that are currently running under the database.  Here is where a neat little snippet might be useful to you!

Let's put this into context...  I have been creating SQL Upgrade Scripts to allow several instances of the same SQL database to exist, while allowing an upgrade and rollback path without have to take the database offline.

When creating these scripts, I often need to rollback my own development instance of the database to begin with a clean slate - so to speak.  Well, creating a T-SQL statement to automatically RESTORE a database is easy.  The IDE does that for you when you are in the Restore Database dialog.

However, you will never be able to restore the database if there are processes still running under it.  Here is where this script comes in:

DECLARE @Database NVARCHAR(150);
-- change this to be the name of your database that you want to kill processes for
SELECT @Database = 'MyDatabaseName';
CREATE TABLE #tempProcesses(
 [spid] [int] NOT NULL,
 [ecid] [int] NOT NULL,
 [status] [nvarchar](150) NOT NULL,
 [loginname] [nvarchar](150) NOT NULL,
 [hostname] [nvarchar](150) NULL,
 [blk] [int] NOT NULL,
 [dbname] [nvarchar](150) NULL,
 [cmd] [nvarchar](150) NOT NULL,
 [request_id] [int] NOT NULL 
INSERT INTO #tempProcesses
EXEC sp_who;
CREATE TABLE #tempProcesses2(
 [spid] [int] NOT NULL
INSERT INTO #tempProcesses2
SELECT [spid] FROM #tempProcesses WHERE [dbname] = @Database;
DROP TABLE #tempProcesses;
DECLARE [cursorProcess] CURSOR FOR SELECT [spid] FROM #tempProcesses2
OPEN [cursorProcess]
FETCH NEXT FROM [cursorProcess] INTO @ProcessId
 EXEC('KILL ' + @ProcessId);
 FETCH NEXT FROM [cursorProcess] INTO @ProcessId
DROP TABLE #tempProcesses2;
CLOSE [cursorProcess];
DEALLOCATE [cursorProcess];

In the above snippet, the code creates a temporary table in memory and loads the current processes into it.  Well, we only need the processes associate to our database, so we create another temporary table for the process ids and load it from the first table.  (And always remember to take great care in dropping the temporary tables.) 

Now, we need to use a cursor to step through the latest table and execute a KILL statement for each ID that matched our database name.

The final step is to clear everything out of memory and - YOU'RE DONE!  Simple, right?

blog comments powered by Disqus