Killing an SQL Process
My recent blog regarding avoiding recursion in your triggers, reminded me of another issue. Specifically, what can you do when something goes wrong and you need to stop a SQL command.
If you muck around with SQL long enough, you will inadvertently have it go into a processing black hole. It could be a recursion condition, or a join clause that is a little too broad and will generate ridiculously huge numbers of logical records, and take hours to run. Whatever the cause, you need to stop the SQL command.
Stopping a SQL command is done via the "KILL " command.
The first thing that needs to be done, is to find the ID of the command that you need to stop. This ID is found by querying the sysprocesses table of the msdb database:
SELECT * FROM [msdb].[sys].[sysprocesses]
This will give you several pieces of information about every active SQL command that is executing. How do you find the specific one that you want to stop? It takes a little bit of detective work. The most useful columns are:
"hostname" which give you the computer name where the command came from
"program_name" which is the name of the program that sent the command
"cmd" which is the command that is running
"loginame" the database login used
Many times you can determine which is the offending command from those. Other times, it may be necessary to monitor the "cpu" (CPU usage), or "physical_io" the amount of disk read/writes, to find the process you want to stop. As I said, sometimes it takes some detective work.
Once you have isolated the particular process, then you can stop it with the "KILL" command. One of the fields from the query to "sysprocesses" is the "spid" field. This is the ID that you will use to stop that process. So, if you determine that the "spid" that you want to stop, is 68, then you would use:
kill 68
This will cause the SQL server to kill the process with an "spid" of 68, and roll back transactions.
Note that rolling back transactions can take some time. For example, say that you sent a command to update the records in a table. Then, you realize that there is a problem, and you kill that command. If, the command had been running for five minutes, doing it’s updates, it will roll back all of those updates that had already processed. In other words, it will likely take another five minutes to roll back all of those updates. The command will finish only after everything was rolled back.
Dave.