Some times it is required to get rid of an orphaned session or to end a session for removal of dead locks. SQL Server provides kill command for this purpose. You required System process ID (SPID) for this purpose. SPID may be get from sysprocesses or sys.sysprocesses
For example to get rid of an orphaned session with SPID 49, we have to issue following command.
Kill 49 GO
This powerful command has some restrictions associated with it. You can not kill your own process. So suicide is not permitted. Also it is not purposeful to kill the processes with following values under cmd column of sysprocesses or sys.sysprocesses.
- AWAITING COMMAND
- CHECKPOINT SLEEP
- LAZY WRITER
- LOCK MONITOR
- SIGNAL HANDLER
Click here to read further about options associated with kill command