|
Execute the following
Microsoft SQL Server T-SQL administration script using Dynamic Management View(DMV) and Dynamic Management Function(DMF) to list all blocking and blocked queries(sessions):
USE AdventureWorks2008;
GO
SELECT BlockedSPID = blocked.session_id,
BlockedQuery = blockedsql.TEXT,
BlockingSPID = blocking.session_id,
BlockingQuery = blockingsql.TEXT
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocking
ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY (SELECT *
FROM sys.dm_exec_sql_text(blocked.sql_handle)) blockedsql
CROSS APPLY (SELECT *
FROM sys.dm_exec_sql_text(blocking.sql_handle)) blockingsql;
GO
Related article:
How to monitor blocking in SQL Server 2005 and in SQL Server 2000
|