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 wait for whole 30 minutes until it gets rollback completely. However, in this type situation, it becomes essential to have an estimate of time required to complete the rollback process or 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:
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 particular session, the most common command to kill SPID in SQL Server which is executed by the users is:
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:
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:
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.
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 undone 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.
Download FREE SharePoint 2019 Installation PDF Guide
This FREE PDF contains, Steps by step guide to install SharePoint 2019 Preview, prerequisites, hardware and software new features. First impression to SharePoint Server 2019 new modern site, modern lists and modern document libraries.