The victim of my carnage being a connection to the SQL Server 2005.
How many times have you issued a query that you had no idea how long it would run, just to loose your patience after 10 minutes and rolled it back, forgetting that it'll probably take 10+ minutes to rollback anyway?
Come on don't be shy... raise your hand... I know you're one of them :)
Although there is no way to get the rollback time from the ROLLBACK command (if there is do let me know) we can use an extended way of calling the KILL command.
KILL command has an option WITH STATUSONLY that tells you how long until will the killed process completes.
We call it like this:
So basically you can do this in one window:
SELECT @@SPID as SpidToKill -- remeber this. It was 60 in my case
BEGIN TRAN
-- our really long query
ROLLBACK
And after you issue a rollback run the following in another window:
KILL 60 -- 60 was the remembered @@SPID from the above
KILL 60 WITH STATUSONLY;
the message you'll get is this:
/*
spid 60: Transaction rollback in progress. Estimated rollback completion: 2% Estimated time left: 130 seconds.
*/
And after running it a few more times you can see that the times are nicely displayed:
/*
SPID 60: transaction rollback in progress. Estimated rollback completion: 7%. Estimated time remaining: 92 seconds.
SPID 60: transaction rollback in progress. Estimated rollback completion: 18%. Estimated time remaining: 81 seconds.
SPID 60: transaction rollback in progress. Estimated rollback completion: 29%. Estimated time remaining: 71 seconds.
SPID 60: transaction rollback in progress. Estimated rollback completion: 94%. Estimated time remaining: 3 seconds.
-- we get this message after the spid has completed dieing
Msg 6106, Level 16, State 1, Line 2
Process ID 60 is not an active process ID.
*/
We can see that the progress report is quite handy to have. and if it takes around 10 minutes you at least know you have time for some coffee.
So don't let those long lasting rollbacks eat your nerves. Happy KILL-ing!
