Help! I do not know how much rollback is pending in my transaction!
This is a very common situation in a DBA’s life where people will be sitting on the neck on him/her and keep asking a same question like “How much more time will it take to complete my rollback?” I experienced this is very typical scenario after some one requested to kill a SPID and then waiting to rerun the same transaction.
Unfortunately we have no way in SQL Server to exactly predict the remaining time. However you have a simple solution to gauge the progress of rollback. In a simple word, just kill it again and you will see the percentage complete of rollback.
Click here for exact technical steps.
From SQL 2005, you can use DMV which is surely a better way but I like this good old way also till now.