Killing a SPID and Checking Rollback progress in SQL Server


By:   |   Updated: 2008-04-09   |   Comments (5)   |   Related: More > Monitoring


Is the Database the Culprit of Your Application Issues?

Free MSSQLTips Webinar: Is the Database the Culprit of Your Application Issues?

When you're troubleshooting application performance issues, have you ever found the problem residing a few layers deep in the database or not at all? How long did it take you to find and fix the issue? Don't worry if you said "a while". Learn how to solve performance problems fast.


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
sql query

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
rollback

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 
jester

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


Last Updated: 2008-04-09


get scripts

next tip button



About the author
MSSQLTips author Armando Prato Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

View all my tips
Related Resources





Comments For This Article




Thursday, March 05, 2015 - 7:30:14 AM - Anderson Back To Top (36452)

Thank you. Very usefull tip!


Tuesday, May 07, 2013 - 1:27:08 PM - dinesh Back To Top (23769)
SPID 140:transtion rollback in process.Estaimated rollback completion:0%
 Estimated time remaining:0 seconds.
 
 
any Solutiuon??

Thursday, April 25, 2013 - 11:42:26 AM - Santosh Kamoli Back To Top (23556)

The Same issue of rollback i am facing while killing session of a tlog job.

 

Error "Transaction rollback in progress. Estimated rollback completion : 0%......."

 

Please recommend the solution or send me work around.

 

Thanks

Santosh Kamoli

 


Friday, August 03, 2012 - 5:32:07 AM - Rajasekhar Back To Top (18900)

Hi

when I use  kill 70with statusonly.this given the fallowing msg "SPID 70: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.".this showing o seconds from last 1 hour and since it has been executing.How can i know Much time still it will take..???


Saturday, March 24, 2012 - 9:37:47 AM - Shamsideen Back To Top (16600)

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



download





Recommended Reading

How to Read Log File in SQL Server using TSQL

How to setup SQL Server alerts and email operator notifications

SQL Server Wait Stats Monitoring with PowerShell

Posting SQL Server Notifications to Slack

SQL Server High CPU Query Use Monitoring with PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools