Killing a SPID in SQL Server and Checking Rollback Progress

How to Kill SPID in SQL Server

The SPID is the identification number that is uniquely assigned to each process whenever a new session is started with the SQL Server. However, when an application is connected to SQL server, a new SPID (or connection) is created. The main purpose of this is to define the memory space and scope and not allowed to interact with other SPID (Server Process ID). It is the term which is used in respect of session or connection also. However, there are some situations, in which it becomes necessary to terminate the particular session and rollback the whole process. Therefore, in the following segments of this article, how to kill a SPID in SQL Server and check rollback progress in SQL Server are discussed.

Consider a Scenario:

The DBA of an organization running a query on a SQL Server and suddenly receives a call from a developer to kill that particular session due to some major issues in a database. The DBA executes the KILL command and waits for a whole 30 minutes until it gets rollback completely. However, in this type situation, it becomes essential to have an estimate of the time required to complete the rollback processor to view SQL Server rollback status.

How to Kill SPID in SQL Server:

When a user runs a query on SQL Server, each value that is added, updated, or deleted is recorded. Therefore, whenever there is a failure or a user needs to kill a SPID in SQL Server, it can be undone in an appropriate manner.

There are are some situations in which a user needs to kill/rollback a SPID then first, they can check all SPIDs. One can easily view the number of connections in SQL server by executing the query:

SELECT *
FROM sys.dm_exec_sessions

Now, there are some situations in which a user needs to Kill the SPID and stop the transaction in between and rollback the whole process. Therefore, to end a particular session, the most common command to kill SPID in SQL Server which is executed by the users is:

sp_who2

After executing the above command, one can easily view the killed SPID in a rollback state, but unable to view the status of the rollback process, as in what it will be completed.

How to Check Rollback Status in SQL Server?

– Why Rollback Takes Time?
As a user executes the KILL command, then each step of the transaction that has been executed earlier needs to be undone. Moreover, the object which the transaction was changing must be the rollback to the previous state from where it starts the transaction or it can be said that the object needs to be restored to its previous state. Apart from all these, the main reason that rollback takes time is that most of the time rollback is single threaded and takes more time.

– Check SQL Server Rollback Status:

As discussed above, most of the user’s searches are related to “query to check the rollback status in SQL Server”. Therefore, to resolve this problem, a user needs to run the KILL command using WITH STATUSONLY argument. It shows an estimated time of completion for an in-progress ROLLBACK. Whenever a user executes the KILL command with the argument, it simply generates a progress report and not actually kill the connection.

To capture the connected SPID and to show that the SPID has an open transaction, execute the following script:

How to Check Rollback Status in SQL Server?
How to Check Rollback Status in SQL Server?

Now, to kill the connection that a user has just opened and to check the state of connection, open a new connection and run the following statements:

How to Check Rollback Status in SQL Server?
How to Check Rollback Status in SQL Server?
How to Kill SPID in SQL Server
How to Kill SPID in SQL Server

To monitor the rollback progress, one needs to execute the KILL command using WITH STATUSONLY as an argument. It will display the current progress of rollback like estimated rollback completion: 0%. estimated time remaining: 0 seconds.

 Check SQL Server Rollback Status
Check SQL Server Rollback Status
 Check SQL Server Rollback Status
Check SQL Server Rollback Status

With the help of all these commands, a user can easily get a good estimation of the when the rollback process will get completed.


There are some situations, in which a DBA or a developer has to end the process in between using KILL command. Therefore, to kill a SPID in SQL successfully, a transaction needs to be rollback completely and bring back to its prior state from where it starts. It basically undid the whole process. Sometimes, the rollback process takes time and a user is not able to estimate the time that the whole process will take to complete. To overcome this issue, we have discussed various commands for killing a SPID and checking rollback progress in SQL Server. A user can use these SQL commands to KILL/ Rollback the session and becomes able to know the estimated time required to complete the rollback process.

Hope this will be helpful.


You May Also like the Following SharePoint Online Tutorials:

About Bijay Kumar

I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SharePointSky.com

View all posts by Bijay Kumar →