join the MSSQLTips community

Today's Site Sponsor


 

Find performance issues related to Analysis Services memory limits.
 


Killing a SPID and Checking Rollback progress in SQL Server
Written By: Armando Prato -- 4/9/2008 -- 0 comments -- printer friendly -- become a member



SQL Server monitoring made easy

        Win SQL Books  -----  SharePoint Tips  -----  Live Webcast - SQL Backup Mistakes  -----  Bookmark and Share        

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
Readers Who Read This Tip Also Read Comment or Ask Questions About This Tip Twitter This Tip!


Sponsor Information
Free SQL Server performance monitoring dashboard – Idera SQL check

Quickly and accurately deploy database changes with Red Gate's SQL Compare – the industry standard comparison and deployment tool.

Is your SQL Server sick? Get on the road to recovery. Innovative SQL DBA consultants.

Looking for SQL Server interview questions and answers?

Learn SQL Server 2008, Performance Tuning, Development, Administration, DR, Replication and more from these web casts

All SQL Server, all the time! Sign-up for the MSSQLTips newsletter!

Do you work on SharePoint too? Check this out...

Free whitepaper - How to Achieve 40:1 Backup Compression with LiteSpeed® for SQL Server’s


 

 



Red Gate Software - SQL Prompt

How can he write SQL so fast? Some developers write SQL amazingly fast. Do you want to know their secret? It’s SQL Prompt. “This is a must-have tool for all T-SQL developers.” Brian Brewder, Brian Online.

Download now!

More SQL Server Tools
SQL Data Generator

SQL Nitro

SQL Refactor

SQL compliance manager

SQL diagnostic manager




Copyright (c) 2006-2010 Edgewood Solutions, LLC All rights reserved
privacy statement | disclaimer | copyright | advertise | write for mssqltips | feedback | about
Some names and products listed are the registered trademarks of their respective owners.