Let us learn more about the SQL server kill spid and how to use it. With the support of our MSSQL support services at Bobcares, we can give you a detailed note on how to use it.

How to use 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.

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

Today, 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.

In this article, you 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;
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:

USE master;
GO
EXEC sp_who 'CSALEM\cs';
GO

The command should return the process information about the set login.

@@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.

SELECT @@SPID AS'id',
    system_user AS'login_name',
USERAS'username'

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.

Then, click on the Process tab to show SPID, login, databases, and more information.

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:

KILL{SESSION ID [WITH STATUSONLY ]| UOW [WITH STATUSONLY | COMMIT |ROLLBACK]}

Conclusion

To conclude we have now learned more about the SQL server kill spid. We have also learned how to use it with the support of our MSSQL support services at Bobcares.