The Transact-SQL KILL command is used to abruptly end a SQL Server process.Each process is often called a System Process ID (spid). The SQL EnterpriseManager Kill Process button under Current Activity merely sends aTransact-SQL KILL command to the server, so the server-side KILL mechanismis the same in this case.
A spid may respond to the KILL command immediately, or after a delay, ornot at all. A delayed or non-responsive KILL command can be normal undersome conditions. This article discusses how the KILL command works, whatthese delayed or non-response conditions are, and how to identify them.
NOTE: This article discusses a DBCC command (DBCC PSS) that is unsupported, and may cause unexpected behavior. Microsoft cannot guarantee that you can solve problems that result from the incorrect use of this DBCC command. Use this DBCC command at your own risk. This DBCC command may not be available in future versions of SQL Server. For a list of the supported DBCC commands, see the "DBCC" topic in the Transact-SQL Reference section of SQL Server Books Online.
Each database connection forms a row in sysprocesses, sometimes called aspid or System Process ID. In SQL Server terminology, each connection isalso called a "process," but this does not imply a separate process contextin the usual sense. In SQL Server 6.0 and 6.5, each process is roughlyanalogous to and serviced by a separate operating system thread. Eachdatabase connection also consists of server data structures that keep trackof process status, transaction state, locks held, and so on. One of thesestructures is called the Process Status Structure (PSS), of which there isone per connection. The server scans the list of PSSs to materialize thesysprocesses virtual table. The CPU and physical_io columns fromsysprocesses are derived from the equivalent values in each PSS.
The Transact-SQL KILL command posts a "kill yourself" message to the spid'sProcess Slot Structure. It appears there as a status bit, which the spidperiodically interrogates. If the spid is executing a code path that doesnot interrogate the PSS status field, the KILL is not honored. Some knownconditions where this situation can occur are given below. Most of theseare considered expected behavior, and are not considered bugs.
Spid Is Waiting on a Network I/O
If the client does not fetch all result rows, the server will eventually beforced to wait when writing to the client. This is seen as asysprocesses.waittype of 0x0800. While waiting on the network, no SQLServer code is being run that can interrogate the PSS and detect a KILLcommand. If the spid holds locks prior to waiting on the network I/O, itmay block other processes.
If the network connection times out or is manually canceled, the SQL threadwaiting on the network I/O will take an error return, thus being freed upto scan its PSS and respond to a KILL. You can manually close a named pipesconnection with the NET SESSION or NET FILES command, or equivalent ServerManager command. Other IPC sessions such as TCP/IP and SPX/IPX cannot bemanually closed, and the only option in this case is adjusting the sessiontimeout for the particular IPC to a shorter value. For more information,see the following article in the Microsoft Knowledge Base:
: How to Troubleshoot Orphaned Connections in SQL Server
The Spid Is Rolling Back (Also Called Being "in Backout")
If the transaction aborts for any reason, it must roll back. If it is along-running transaction, it may take as long to roll back as to apply thetransaction. This includes long-running implicit transactions such assingle SELECT INTO, DELETE, or UPDATE statements. While it is rolling backit cannot be killed; otherwise, the transactional changes would not bebacked out consistently.
The unkillable rollback scenario can often be identified by observing thesp_who output, which may indicate the ROLLBACK command. On SQL Serverversion 6.5 Service Pack 2 or later, a ROLLBACK status has been added tosysprocesses.status, which will also appear in sp_who output or the SQLEnterprise Manager "current activity" screen. However, the most reliableway to get this information is to inspect the DBCC PSS of the blocking SPIDin question, and observing the pstat value. For example:
dbcc traceon(3604) /* Return subsequent DBCC output to the client rather than to the errorlog. */ goSELECT SUID FROM SYSPROCESSES WHERE SPID=<unkillable SPID number>goDBCC PSS (suid, spid, 0) /* Where suid is from above, and spid is the unkillable SPID number. */ go
The first line of returned information will contain the pstat value.
For example, it may be something like the following:
pstat=0x4000, 0x800, 0x100, 0x1
Meaning of pstat bits:
0x4000 -- Delay KILL and ATTENTION signals if inside a critical section0x2000 -- Process is being killed0x800 -- Process is in backout, thus cannot be chosen as deadlock victim0x400 -- Process has received an ATTENTION signal, and has responded by raising an internal exception0x100 -- Process in the middle of a single statement transaction0x80 -- Process is involved in multi-database transaction0x8 -- Process is currently executing a trigger0x2 -- Process has received KILL command0x1 -- Process has received an ATTENTION signal
The pstat value above would be a typical situation if a long-running datamodification was canceled (for example, by clicking the Cancel Query buttonon a GUI application), and then the SPID was found (for a time) to blockusers, yet be unkillable. This situation is normal; the transaction must bebacked out. It can be identified by the bits, as noted above.
Spid 1 Has a Status of 0000 (Running Recovery)
When starting (or restarting) SQL Server, each database must completestartup recovery before it can be used. This is seen as the first spid insp_who having a status of 0000. It cannot be killed, and recovery should beallowed to run to completion without restarting the server. Only user spidscan be killed, not system spids such as lazywriter, checkpoint, RA Manager,and so on. You also cannot kill your own spid. You can find which is yourspid by doing SELECT @@SPID.
Server Has Intentionally Delayed Honoring KILL
In a few situations, the server intentionally defers acting on a KILLcommand or ATTENTION signal (a query cancellation request). An example ofthis is while in a critical section. These intervals are usually brief.This situation can be seen as a pstat value of 0x4000.
Code Path Does Not Check for KILL
If you have eliminated each of the above scenarios, it is possible that thecurrent code path simply is not checking for KILL. For example, before SQLServer version 6.5 Service Pack 3, DBCC CHECKDB did not reliably respond toKILL because certain code paths did not check it. If all of the abovesituations have been excluded (that is, the user process is not waiting onI/O and not in rollback, the database is not in recovery, and SQL Server isnot intentionally deferring KILL) yet KILL is not being honored, it may bepossible to enhance the server so that KILL works. To make thisdetermination, each case must be individually examined by your primarysupport provider.
The fact that the message "Process id 10 killed by Hostname JOE" is writtento the errorlog does not confirm the KILL has actually taken place. Thismessage is written immediately following making the kill request, but doesnot signify that the KILL has been honored.