Killing a SPID and Checking Rollback progress in SQL Server

By: Armando Prato | Read Comments (1) | Print Armando has over 24 years of industry experience and has been working with SQL Server since version 6.5. Related Tips: More

Problem
From time to time, I encounter blocking situations where I end up issuing a KILL command against the blocking SPID. When I run sp_who2, I can see the killed SPID is in a ROLLBACK state. Is there any way I can get an idea of how long the rollback for the SPID will take?

Solution
There sure is! The KILL command offers the WITH STATUSONLY argument which displays an estimation of completion for an in progress ROLLBACK. Using KILL with this argument does not kill a connection; it simply produces a progress report.

Run the following script in a new query window. It captures the connected SPID for the query and displays the fact that the SPID has an open transaction when the script completes:

set nocount on go select 'SPID = ' + cast(@@spid as varchar) -- what connection is this? go create table test (col1 int identity) -- create a test table go begin tran tran1 -- start a transaction while (1 = 1) begin insert test default values if scope_identity() > 999999 -- break after inserting 1m rows    break end go dbcc opentran(0) go

Now open a new connection and issue the following statements to kill the connection we just opened and to examine the connection's state:

kill 52 go exec sp_who2 go

Now that we've KILLed the SPID, we can monitor its rollback progress by issuing KILL using the WITH STATUSONLY argument.

kill 52 with statusonly

As you can see, you can get a good estimation from the server of rollback progress.

Next Steps

• Read about KILL UOW in the SQL Server Books Online
• Read about DBCC OPENTRAN in the SQL Server Books Online

Related Tips: More | Become a paid author

Last Update: 4/9/2008

Share:

Share

Tweet

Free SQL Server Learning Waiting on Waits Let's connect on facebook! Tweet...tweet...tweet... Follow us on twitter

Comments and Feedback:

Saturday, March 24, 2012 - 9:37:47 AM - Shamsideen	Read The Tip
I ahve been having issue with SQl Server 2008 installed on Windows serv er 2008 R2.  The SQL takes most of the memory. the size out DB is about 52 GB and we have  8 GB RAM installed. Please help out. This the output of DBCC command

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Try the free performance monitoring tool from Idera! SQL Monitor, server monitoring so easy, your boss could do it. Try it online. SQL Servers keeping you up at night? Contact expert SQL Server consultants for a Health Check. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Join the over million SQL Server Professionals who get their issues resolved daily. Free web casts for DBAs and Developers on Performance Tuning, Development, Administration and more....