Stopping Processes

A process is a task being carried out by SQL Server. Processes can be initiated either by a user executing a statement or by SQL Server itself.

Each process is assigned a unique (but not necessarily consecutive) system process identification number (spid) when it starts. A process can determine its own spid by executing SELECT @@SPID. Process identification numbers and other information about each process are stored in the sysprocesses table in the master database.

Note Each spid identifies a connection to SQL Server. Some client applications establish several connections.

To keep the system operating smoothly, you might want to check ongoing processes regularly with the sp_who system procedure. Another system procedure, sp_lock, also reports on processes. It gives information about all the locks currently held on SQL Server, including the spid of the process holding each one. For additional information about sp_who and sp_lock, see the Microsoft SQL Server Transact-SQL Reference.

The KILL statement is used to terminate an ongoing process. Only the system administrator can issue the KILL statement (permission to use it cannot be transferred). The most frequent reason for stopping a process is that it interferes with other users. A KILL statement is not reversible and cannot be put inside a user-defined transaction.

You cannot stop system processes. Server processes reported by sp_who and sp_lock include CHECKPOINT, MIRROR HANDLER, LAZY WRITER, and RA MANAGER. Any other process can be stopped with the KILL statement. You can stop only one process at a time.

    To stop a process

• From a client utility such as isql, type:

  KILL spid

  where:

  spid

  Is the identification number of the process you want to kill. You can get a list of process ID numbers using the sp_who system procedure.

  For example, to kill process ID 14, type:

  KILL 14