Wednesday, 23 January 2013

TSQL : Processes being blocked

How to determine the Processes being blocked via TSQL

Old way : exec sp_who 80
New way (sql 2005+): SELECT * FROM sys.dm_exec_requests WHERE Session_id = 80

With sp_who the column blk holds the session id of the blocking process.

On the sys.dm_exec_requests view there is the blocking_session_id column.

To stop a blocking process, use KILL spid e.g. KILL 71

Other uses of sys.dm_exec_requests -

Solace : Long Running Queries
Solace : % Percentage complete of running requests

MSDN : sys.dm_exec_requests
MSDN : sp_who

No comments: