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 a 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 that 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.
SharePoint Tutorial Contents
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:
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 in 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 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.
You may like following SQL Server tutorials:
- windows successfully diagnosed a low virtual memory condition sql server event id 2004
- Create Windows Virtual Machine in Microsoft Azure Step by Step Guide
- Download and install GitHub for windows (‘git’ is not recognized as an internal or external command)
- How to create a new user in windows server 2016
- How to install and configure Active directory windows server 2012 r2
- Install and configure active directory and a domain controller in windows server 2016
- Download and Install SQL Server 2014 SP1 in Windows Server 2016
- How to enable hibernate in windows server 2016?
- How to enable audio service in Windows Server 2016 or Windows Server 2012 r2?
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 SPGuides.com