MS SQL Server

SQL Server Kill SPID

SPID or SQL Server Process ID is a unique value assigned to a session when connecting to a SQL server instance. For example, if you use a tool such as JetBrains Data Grip to connect to the SQL Server, the SQL server assigns a unique session ID that contains standalone memory space and scope. This means that the SPID cannot interact with other SPIDs on the server.

SQL Server uses SPID values below 50 for internal server processes, while any SPID above 51 (inclusive) value is assigned to user processes.

In this guide, you will understand how to get SPID values in SQL Server and how you can kill a transaction using its SPID.

SQL Server Show SPID

In SQL Server, there are various ways to get the SPID of the running processes. This guide will look at the most common and easy to use.

sp_who (Transact-SQL)

The sp_who is a helper procedure that allows you to view user information, sessions, and the SQL Server instance processes. Using this procedure, you can filter for specific information, such as the username and the SPID value.

The syntax is as shown:

sp_who [ [ @loginame = ] 'login' | SESSION ID | 'ACTIVE' ]

The login refers to the sysname that identifies a process for a specific login.

The session ID is the SPID value to a specific process connected to the SQL Server.

Example Usage

The following commands show how to use the sp_who procedure in SQL Server.

Show all current processes

To display all current processes, use the query as shown:

USE master;
EXEC sp_who;

The query above should return the information with columns such as SPID, ECID, STATUS, LOGINAME, and more.

Show process for a specific user

To get the processes associated with a specific user, we can specify the username in the query as shown in the example below:

USE master;
EXEC sp_who 'CSALEM\cs';

The command should return the process information about the set login. An example output is as shown:


Another method you can use to get the SPID of a process is using the @@SPID function. This configuration function returns the session ID of the current process.

Example Usage

The following example shows how to use the @@SPID to fetch information about a specific process.

    system_user AS 'login_name',
    USER AS 'username'

The query returns the spid, loginame, and username. Example output is as shown:

Activity Monitor

You can also use a graphical method to view the Session ID value for a specific process. Launch the MS SQL Server Management Studio and right-click on the SQL Server instance. Open Activity Monitor.

Click on the Process tab to show SPID, login, databases, and more information. The information displayed by the activity monitor is as shown:

SQL Server Kill SPID

Sometimes, you may encounter a specific instance running slow, blocking other processes, or consuming system resources. You can end the process using its SPID.

The KILL SPID command allows you to specify a specific user SPID value and terminate the process. Once you call the command, SQL Server will execute a rollback (undo changes) process; hence may take some time to terminate an extensive process.

The following shows the syntax of the KILL SPID command:


Example Usage

Pass the Session ID value to use the KILL SPID command (learn how to get the SPID value above).

KILL 90;

The above command should stop the specified process.

To get the progress of the process termination, you can use the WITH STATYSONLY argument as shown:


The command above should return the progress as:

Msg 6120, Level 16, State 1, Line 1
The STATUS report cannot be obtained. ROLLBACK operation FOR Process ID 84 IS NOT IN progress.

The example below shows you how to terminate a running database backup using the KILL SPID command.

Start backup as:

backup DATABASE [WideWorldImporters]
    TO disk=N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\WWI_Backup.bak'
    WITH noformat, name=N'\WWI_Backup', compression, stats=10;

Using the KILL SPID command, end the backup process.


In this article, you learned how to work with SQL Server Session IDs. Various methods to fetch the SPID value and end a process using the SPID value.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list