Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 KILLed 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.

share|improve this question

migrated from stackoverflow.com Apr 21 '11 at 17:25

This question came from our site for professional and enthusiast programmers.

    
You'll get better answers on ServerFault.com –  Raj More Apr 21 '11 at 15:58
    
We've seen the same recently... in our case the blocking statement is listed as "--" in the blocking transactions report as well as DBCC INPUTBUFFER (spid). Still trying to figure out how this is posisble. It's a third-part application, and I suspect a transaction nesting issue, but the app-layer code is closed to us and what we can see at the DB layer is a spaghetti mess. –  rmalayter Dec 5 '12 at 20:09

2 Answers 2

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]

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO
share|improve this answer

try with sp_who2
you can observe what is going on your db

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.