Problem
From time to time, I encounter blocking situations where I end up issuing a SQL KILL command against the blocking SPID (Server Process ID). 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 SQL 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.
SQL Server Blocking Scenario
Run the following script in a new SQL Server Management Studio 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
Kill SQL SPID
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
SQL KILL with STATUSONLY
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
- Read about DBCC OPENTRAN
For 20+ years, MSSQLTips.com has been delivering comprehensive technical solutions on a daily basis for the Microsoft SQL Server platform. In that time, an innovative team of over 300 authors from around the globe have delivered over 7000 articles and 1000 webcasts ranging from beginner topics to advanced solutions. Our community prides itself on delivering human only generated content.
Useful. Thanks 🙂