Alert Based Automation of WhoIsActive with XML attachment

I've been dealing with a system that has a lot of blocking.  I've found Adam Machanic's Who is Active to be an invaluable resource.

In an effort to avoid the bleary eyes syndrome in the small hours, I started to investigate how to receive automated XML attachments of the output of Adam's stored procedure.

Below is how I set this up on my system:

Since my original post I've updated the script, to remove the lead blocker option which can cause this error:
"The statement terminated. The maximum recursion [x] has been exhausted before statement completion."

Step 1 - Define the Who is Active parameters

sp_WhoIsActive includes a variety of parameters.  My first step was to identify the information I want to capture.  (Additional details for the various parameters can be found in the comments of the stored procedure).

@find_block_leaders = 1 
Since I'm troubleshooting blocking issues I included the optional @find_block_leaders = 1:

@output_column_list= '[start_time][sql_text][session_id][login_name][CPU%][block%][reads%][writes%][wait_info][open_tran_count][database_name]' 
Who is Active can return a LOT of information.  I selected specific columns to trim down the data.

@format_output = 0 
0 disables output format.  I found this helpful when first debugging piping the results to an XML file.

@destination_table = 'WhoIsActive' 
The job that I'm ultimately creating will first execute sp_WhoIsActive, dumping the results to a table.    This parameter tells sp_WhoIsActive which table to store the results.

Step 2 - Create a table to store the Who is Active output

Because I want to format the output as an XML file I chose to store the output of Who is Active in a local table that I could reference later.  The table design is dependent on the output parameters from Who is Active.

First I determined which values I would be outputting from Who is Active, see @output_column_list  above.  Then I built a table to store the output:

CREATE TABLE [dbo].[whoisactive]
  (
     [start_time]            [DATETIME] NULL,
     [sql_text]              [XML] NULL,
     [session_id]            [SMALLINT] NULL,
     [login_name]            [NVARCHAR](128) NULL,
     [CPU]                   [VARCHAR](30) NULL,
     [blocking_session_id]   [SMALLINT] NULL,
     --[blocked_session_count] [VARCHAR](30) NULL,
     [reads]                 [VARCHAR](30) NULL,
     [writes]                [VARCHAR](30) NULL,
     [wait_info]             [NVARCHAR](4000) NULL,
     [open_tran_count]       [VARCHAR](30) NULL,
     [database_name]         [NVARCHAR](128) NULL
  )
ON [PRIMARY]
textimage_on [PRIMARY] 

I set the datatypes based on their corresponding definitions from the Who is Active stored procedure.

Step 3 - Finalize the sp_WhoIsActive parameters

Finalizing the stored procedure parameters required that I map the Who is Active output to the table definition and pass the parameter identifying that I want the output to go to a table.  Below is the stored procedure execution with the parameters defined:

EXEC sp_WhoIsActive
@find_block_leaders = 1,
@output_column_list=
'[start_time][sql_text][session_id][login_name][CPU%][block%][reads%][writes%][wait_info][open_tran_count][database_name]',
@format_output = 0,
@destination_table = 'WhoIsActive' 

If you're following along with SSMS open, and have sp_WhoIsActive installed, you should be able to run a quick test at this point.  With your table created, start an update transaction in a debugger session with a break point on the commit.  With the debugger paused at the commit attempt to run the same update.  At this point there should be a block occurring and you can execute the above sp_WhoIsActive statement and get some data in the WhoIsActive table that we created.

Step 4 - Create a stored procedure to output XML

I found it not so simple to get an XML file attachment just by execution of sp_WhoIsActive from sp_send_dbmail.  My workaround was to create another stored procedure which I named sp_WhoIsActiveXMLout.  This stored procedure simply outputs the data from our WhoIsActive table in an XML format, with a root node added to the XML named <WhoIsActive>.  Below is the definition for sp_WhoIsActiveXMLout:

Since the original post I've updated this stored procedure to address the following error:"XML parsing: line 17, character 19, illegal qualified name character. "

If some non-XML compatible characters get into a query the sp_WhoIsActiveXMLout will choke, so I rewrote it to use CDATA:

USE [DBA]

go

SET ansi_nulls ON

go

SET quoted_identifier ON

go

CREATE PROCEDURE [dbo].[Sp_whoisactivexmlout] @bodyXML NVARCHAR(max) output
AS
  BEGIN
      SET nocount ON;
      SET ansi_warnings OFF
      SET @bodyXML = --@bodyXML 
      +(SELECT 1                                  AS Tag,
               0                                  AS Parent,
               [start_time]                       AS [Row!1!start_time!CDATA],
               CONVERT(NVARCHAR(max), [sql_text]) AS [Row!1!sql_text!CDATA],
               [session_id]                       AS [Row!1!session_id!CDATA],
               [login_name]                       AS [Row!1!login_name!CDATA],
               [cpu]                              AS [Row!1!CPU!CDATA],
               [blocking_session_id]              AS
               [Row!1!blocking_session_id!CDATA], 
               [reads]                            AS [Row!1!reads!CDATA],
               [writes]                           AS [Row!1!writes!CDATA],
               [wait_info]                        AS[Row!1!wait_info!CDATA],
               [open_tran_count]                  AS
               [Row!1!open_tran_count!CDATA]
               ,
               [database_name]                    AS
               [Row!1!database_name!CDATA]
        FROM   dba.dbo.whoisactive
        FOR xml explicit, root('WhoIsActive'))

      RETURN
  END

go 

Step 5 - Putting the SQL Agent Job Together

Now we want to create a job to execute Who is Active and email us an XML attachment containing the details.

First we need to create a Job in SQL Server Agent

Step one of the job is the above sp_WhoIsActive execution with the variables we defined:
EXEC sp_WhoIsActive
@find_block_leaders = 1,
@output_column_list=
'[start_time][sql_text][session_id][login_name][CPU%][block%][reads%][writes%][wait_info][open_tran_count][database_name]',
@format_output = 0,
@destination_table = 'WhoIsActive'; 

Step two of the job is the XML translation and file attachment (I'm assuming you already have Database Mail configured)

EXEC msdb.dbo.sp_send_dbmail
@recipients='[email address]',
@profile_name = '[Database Mail Profile]',
@subject = 'Who Is Active',
@body_format = 'TEXT',
@query = 'SET NOCOUNT ON; SET ANSI_WARNINGS OFF; DECLARE @bodyXMLreturn nvarchar(max) EXEC dbo.sp_WhoIsActiveXMLout @bodyXMLreturn OUTPUT select @bodyXMLreturn',
@execute_query_database = '[Database where the above table was created]',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'WhoIsActive.xml',
@query_result_header = 0,
@query_result_width = 32767,
@query_result_separator = '',
@exclude_query_output = 0,
@query_result_no_padding = 0,
@query_no_truncate=1; 

Step three of the job is to truncate the WhoIsActive table so that multiple executions of the job return only the distinct blocking information of the most recent issue.

TRUNCATE TABLE WhoIsActive; 

Here's a screenshot of the job steps:

Step 6 - Trigger the job execution

This job, although helpful for running on demand, is ultimately designed to be triggerd by an event of some sort.  In my particular case I'm interested in getting more details when blocking ocurrs.  Therefore, I setup a SQL Agent alert which runs the job when the following situation occurs:

In the Response section of the alert properties I checked the Execute job and selected the job created in Step 5.

One other very important setting is on the Options section of the alert properties.  In order to avoid getting seriously spammed it's important to set the 'Delay between respones' option.  I set mine to 15 minutes.

That's it.  Now when the SQLServer:General Statistics > Processes blocked counter rises above 10 a job will be started that executes sp_WhoIsActive, dumps the results to a table, re-outputs the results with an XML root node, emails the XML file, and finally truncates the results table.

There are a veritable variety of ways this can be configured, from the data you want to capture from sp_WhoIsActive to the counter you wish to use for the SQL Alert.  I hope this helps you as much as it's helped me.  And of course all the credit goes to Adam Machanic for creating Who is Active in the first place.