WMI Provider for Server Events - Professional SQL Server 2008 Administration with Windows PowerShell - page 238

CREATE PROCEDURE getBlockedProcessesDetails
AS
SELECT session_id, blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
GO
ALTER PROCEDURE getBlockedProcessesDetails
AS
SELECT session_id, command, blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
GO
DROP PROCEDURE getBlockedProcessesDetails
The query first creates a stored procedure called getBlockedProcessesDetails . This stored procedure
gets a list of blocked and blocking sessions. Then the stored procedure is modified to include the T-SQL
command from the blocked session. Finally, the stored procedure is dropped.
As shown in Figure 10-9, the MonitorStoredProcs.ps1 script captures all three events.
Figure 10-9