Search sqlserver-dba.com

Follow sqlserver-dba.com

*Use the Comments section for questions

SQLServer-DBA.com Links

How to monitor a SQL Server Rollback with sys.dm_exec_requests

22 November,2011 by Tom Collins

SQL Rollback Transaction is easy with sys.dm_exec_requests. The dynamic management view (DMV) sys.dm_exec_requests returns information about each request that is executing within SQL Server.

Instead of using Activity Monitor to view the status of a Rollback , use this T-SQL code.

Activity monitor is based on window panes and is cumbersome to export any information.

Using t-sql allows extra flexibility in reporting.

Note (13 th Sept,2019 ) - Matthias in comments below suggested to add der.reads and der.writes as part of the recordset. This will assist in presenting a more granular breakdown of Rollback progress - so even though it percentage complete may be on certain % - the reads and writes will continue to change

select 
der.session_id, 
der.command, 
der.status, 
der.percent_complete,
der.reads,
der.writes
from sys.dm_exec_requests as der
where command IN ('killed/rollback','rollback')

Read more on tracking SQL Backups and INDEX reorganisations

SQL Server - Predict SQL BACKUP DATABASE finish time with sys.dm_exec_requests

Predict ALTER INDEX REORGANIZE finish time

Author: Tom Collins (http://www.sqlserver-dba.com)

Share:

Posted by Tom Collins at 8:45 AM in DBA Scripts, DMV | Permalink | Comments (8)

Cool script.

I use that DMV regularly for backups, restores.... but never thought to use it to monitor a rollback.

Posted by: Parvinder NIjjar | Feb 5, 2015 12:34:20 AM

Hi, very useful during big data loads

Posted by: Jack Vamvas | Feb 6, 2015 12:32:26 AM

Thanks!!, your post save me from a stress attack. I had a KILLED/ROLLBACK issue, so I was able to monitor the completed percentage of the process...after 3 hours it finished, but without that info those 3 hours would have been a nightmare...

Posted by: Sergio | Dec 16, 2016 7:55:25 AM

Sergio, thanks for the feedback , glad you survived!

Posted by: Jack Vamvas | Dec 21, 2016 8:40:52 AM

Hello Jack,

Would it work in all scenarios? Like, if i kill any user transaction and it's rolling back, Do i know the percent_complete?

Posted by: Krunal Patel | Jan 15, 2018 10:47:16 PM

Krunal - yes , it will display the rollback . Another command you can use is KILL WITH STATUSONLY. This command won't kill but it will display the percent complete of a KILL COMMAND

Posted by: Jack Vamvas | Jan 20, 2018 8:30:29 AM

If it is a larger rollback, it may seem to be stuck and
not proceeding - 0% - esp. in the beginning.
You might think - what is going on ?
I propose to include the columns "der.reads", "der.writes" and watch them progressing slowly (re-run the query) - most of the time the status will be suspended (due to I/O wait), but in this way you see that at least something is progressing.

Posted by: Matthias P. | Sep 12, 2019 1:25:28 AM

Matthias - thanks for the suggestion - i've added the two extra columns above and added a Note for the suggestion

Posted by: Jack Vamvas | Sep 13, 2019 12:54:52 AM

Verify your Comment

Previewing your Comment

Posted by: |

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:

Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.