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:
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:
GO
EXEC sp_who;
GO
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:
GO
EXEC sp_who 'CSALEM\cs';
GO
The command should return the process information about the set login. An example output is as shown:
@@SPID
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).
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:
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:
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;
GO
Using the KILL SPID command, end the backup process.
Conclusion
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.
