Seeing progress of rollbacks



Seeing progress of rollbacks

I was working on a procedure today that was taking hours to run due to a far larger amount of data than expected, so I kept stopping the query.  In 2000 you could see that a process was being rolled back, but how long would it take?  Too often the three finger salute was given to machines that seemed to be hung up doing nothing, when really the server was chugging along doing work.  Best it seemed you could do was watch perfmon and see what was going on.
Yesterday I gave you the query I have been using to watch progress (after, ahem some editing) and today a much (much) smaller example.  There are lots of columns in the dynamic management views I used for the other procedure that are only useful in a few conditions.  The most useful of these is percent_complete.  Good for some activities, as books online states: "including rollbacks."  Well, ok, a list of all activities that is did cover would be nice, but I was wanting to watch a rollback, so good enough. 
This query showed me the percentage complete of my really large rollback, which was very useful:
select der.session_id, der.command, der.status, der.percent_complete
from sys.dm_exec_requests as der
where status = ‘rollback’
     or  session_id = -1 –change to the spid you are interested in if you don’t see the progress you want
Edit: Added session_id to the query since if you don’t see the rollback you want you will want to go hunting
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s