About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Monday, July 09, 2012

SQL-Server Query to get blocking information

A useful query to get the blocking information within SQL-Server:

SELECT
tr1.resource_type,
tr1.resource_subtype,
tr1.resource_database_id,
tr1.resource_associated_entity_id,
tr1.request_mode,
tr1.request_type,
tr1.request_status,
tr1.request_session_id,
tr1.request_owner_type,
tr2.blocking_session_id
FROM sys.dm_tran_locks as tr1
INNER JOIN sys.dm_os_waiting_tasks as tr2 ON tr1.lock_owner_address = tr2.resource_address;

-Source: Microsoft SQL Server 2012 - Pocket Consultant by William R. Stanek