Blocked Process - Alerting

john.trumbul

I have written some T-SQL that will return the results of a blocked process by producing the entire blocking chain. Since we are only allowed to return one result in custom alerts can someone help me out. I would like to be able to use the results of this query which can be used to identify the chain of a blocked process, and I am not sure how to go about it.

SELECT  waiting_task_address,

        session_id,

        exec_context_id,

        wait_duration_ms,

        wait_type,

        resource_address,

        blocking_task_address,

        blocking_session_id,

        blocking_exec_context_id,

        resource_description

FROM    sys.dm_os_waiting_tasks

WHERE   blocking_session_id IS NOT NULL;

GO

This is both the condition to fire an alert, and the information most helpful in the alert resolution. I would like to create an alert that runs this, and if any blocked processes are returned. Send an email with the information returned from the above.

mandevil

We can only process 2 columns in our alerts, so the first column has to be a concatenation, then you'd have the numeric.

Define the alert as a custom multiple numeric return.

This will run, but you will likely run into an issue with the size of the text should you actually encounter blocking.

We limit the text to 100 characters currently. 8 (

So, you will have to consider some short-hand notation (using something like the TRIM function) to give you what you need and still get the info you need.

SELECT  waiting_task_address + session_id + exec_context_id + wait_type + resource_address + blocking_task_address + blocking_session_id + blocking_exec_context_id + resource_description,

        wait_duration_ms

FROM    sys.dm_os_waiting_tasks

WHERE   blocking_session_id IS NOT NULL;

GO