My SQL Server 2008 started hanging on simple queries, so I checked for blocking transactions, using the built-in All Blocking Transactions report. Sure enough, such a transaction exists. However, if I expand Blocking SQL Statement, I get a blank box. How can I find out more about the problem?
This transaction is blocking several jobs which are business-critical (well, not really, but the boss and the boss's boss depend on them), so I reluctantly KILL
ed the blocking session. 20 minutes later, it's still "rolback in progress… completion: 0%… estimated time remaining: 0 seconds." I'd really like to know what's causing this, I haven't had a single noticeable block for several months.
Best Answer
What version of SQL are you on? If 2005 and above, try to identify the offending query using below script? [Disclaimer: I am not the original author of the script; i had it in my library for a while but i couldn't give proper credit for the original author since i couldnt remember where i get it]