Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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.