KILL SPID command in SQL Server

This article gives an overview of the KILL SPID command and how to monitor the rollback progress.

Introduction

Once we connect to a SQL Server instance, it assigns a unique identification number to each connection. It is the Server Process ID and commonly known as SPID. SPID 1 to 50 is reserved for SQL Server internal processes, and the user process starts from SPID 51.

Different ways to check the SPID in SQL Server

You can check all processes in SQL Server using the following ways.

Using SP_who2 command:

Execute the sp_who2 command, and it lists out all the SPID’s including the system and user processes.

Using Dynamic Management View:

We can use the DMV sys.dm_exec_sessions to get the session details.

SELECT *

FROM sys.dm_exec_sessions;

By default, it shows all processes in SQL Server. We might not be interested in the system processes. We can filter the results using the following query.

SELECT *

FROM sys.dm_exec_sessions

WHERE is_user_process = 1;

Using Activity Monitor:

Connect to a SQL instance in SSMS and, right-click on SQL instance. Launch Activity Monitor. It opens the following Activity Monitor that contains Overview, Processes tabs.

In the Processes tab, we can view the SP ID and details such as login, database, application, hostname. By default, Activity Monitor displays the User processes as shown in the following image.

If we want to view all SPID’s, set the User Process column value from the drop-down to All (1 – User Process, 0- System process).

Using SSMS information bar:

Once we open a new session in SSMS, it shows the following information in the information bar.

Connection Status
Instance name in which we are connected
User name (SPID). In the following image, we can see that we are connected with Kashish\Test(84). In bracket, it is a SPID. It is 84 in our case

The @@SPID global variable:

We can use the global variable to know about the session id of the currently connected session. It is useful if we connect to SQL Server using the command line tools such as SQLCMD.

1	SELECT @@SPID AS CurrentSPID;

Overview of KILL SPID command in SQL Server

Suppose you receive a call that the particular SQL instance is running slow. You start troubleshooting and identify a SPID causing blocking for other SPID’s, and it is taking many system resources. You require terminating this query to release the high consuming resources a remove blocking due to this session.

You can use the KILL SPID command to kill a particular user session. You can only KILL the user processes. Once we kill a session, it undergoes through the rollback process, and it might take time and resources as well to perform a rollback.

Before we look more on KILL SPID, we need to understand that Microsoft SQL Server follows the ACID properties. All transactions should meet the ACID criteria.

Atomic: Transaction should be either complete or fail. There should not be any transaction in a status other than this
Consistent: The database should be consistent before and after the transactions
Isolated: Multiple transactions should run, but it should not put the transactions in inconsistent mode
Durability: All transactions should be durable. Once a record is committed, it should remain committed regardless of the system failure. SQL Server needs to maintain a failed or uncompleted transaction to roll back in case of any failure

Once we KILL SPID in SQL Server, it becomes an uncompleted transaction. SQL Server must undo all changes performed by the uncompleted transaction and database objects should be returned to the original state before these transactions. Due to this reason, you can see the status of SPID as KILLED\ROLLBACK once the rollback is in progress. Rollback might take less time in most of the case; however, it entirely depends upon the changes it needs to rollback.

Let start an active transaction for inserting a large number of rows in the SQL table.

CREATE TABLE [dbo].[tblSQLShackDemo](

[S.No.] [int] IDENTITY(0,1) NOT NULL,

[value] [uniqueidentifier] NULL,

[Date] [datetime] NULL

) ON [PRIMARY]

ALTER TABLE [dbo].[tblSQLShackDemo] ADD DEFAULT (getdate()) FOR [Date]

Begin transaction

Declare @Id int

Set @Id = 1

While @Id <= 1000000

Begin

Insert Into tblSQLShackDemo(value) values (newid())

Set @Id = @Id + 1

End

Check the status of the query using the SP_who2 command.

After some time, use the KILL command to KILL SPID using the following command. Execute this command in a new query window.

KILL 84

Once we execute the KILL SPID command, SQL Server starts the ROLLBACK process for this query. You can see the Status as ROLLBACK in the following image.

We might be interested to know the rollback time. We can check the estimated roll back time using the following command:

KILL SPID with STATUSONLY

We want to check the ROLLBACK status for the SPID 84, therefore, execute the query.

KILL 84 with STATUSONLY

The KILL command with STATUSONLY does not kill any process. It returns the estimated return time for the rollback in seconds.

In the following screenshot, you can estimate completion time is 34 seconds. You need to execute this query again to get the updated time.

In another test, I inserted more records in the table and KILL the session before completion. In the following screenshot, we can see the estimated roll back time 3567 seconds (approx. 1 hour).

Use KILL SPID command to eliminate blocking in SQL Server

We might face blocking in SQL Server, and sometimes we require KILLING the blocker SPID. In SQL Server, we get blocking issues when a SPID holds a lock on a specific resource and another SPID tries to acquire a conflicting lock on the same resource.

Firstly, let’s generate a blocking scenario. Execute the following query in SSMS session. The SPID for this session is 60.

1 2	Begin transaction Update tblSQLShackDemo set value=newid()

In another session, it tries to get records of the table. The SPID for this session is 83.

1	Select * from tblSQLShackDemo

The select statement keeps running and does not return any rows. You can see the status as Executing Query in the following screenshot.

Let’s wait for more time, but still, the query shows the status as Executing Query.

We can check the blocking using the sp_who2 command or using the DMV sys.dm_exec_requests.

Execute the following DMV in another query window of SSMS.

SELECT blocking_session_id ,*

FROM sys.dm_exec_requests

WHERE blocking_session_id <> 0;

In the output, we can see that session id 83 (Select statement) is blocked due to session id 60 ( Update statement).

Due to blocking, we do not get any result for the Select statement. We need to terminate SPID that is causing the blocking using the KILL SPID command. In my example, SPID 60 is the culprit, so we need to kill it. Execute the following query.

KILL 60

Once we KILL the blocking session id, it returns the result for the Select statement immediately.

Note: KILL SPID should be a temporary solution; we need to look at the queries causing blocking and tune the queries to fix performance issues

Terminate running database backup using a KILL SPID command

Suppose a Junior DBA started a full database backup for a large database in TB’s during business hours. It is taking system resources, and you do not want to continue with it.

For this demonstration, start database backup using the following script.

BACKUP DATABASE [AdventureWorks2017]

TO DISK = N'C:\SQLShack\AdventureWorks2017_2019_06_01_07_39.bak'

WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2017-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10;

While the backup is in progress, execute the sp_who2 command to see the backup database process and note down the SPID for it.

Let’s assume we want to cancel the backup. Execute the KILL SPID command, and it immediately kills the backup process.

You get the following message in the backup database query window. It terminates the backup process, and you can see that the session is also in the disconnected state.

Conclusion

In this article, we explored the KILL SPID command to terminate a service process (SPID) and monitor the rollback status. You should use this command carefully in the production instance.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023