Analyzing blocked process report XML

CREATE TABLE #BlockingDetails
(
alerttime datetime,
Nature                        VARCHAR(100),
waitresource    VARCHAR(100),
spid    VARCHAR(10),
waittime                VARCHAR(100),
transactionname         VARCHAR(100),
lockMode                VARCHAR(100),
status                        VARCHAR(100),
clientapp               VARCHAR(100),
hostname                VARCHAR(100),
loginname               VARCHAR(100),
currentdb               VARCHAR(100),
inputbuf                VARCHAR(1000),
spname              VARCHAR(1000)
)

–Blocked process details

INSERT INTO #BlockingDetails
SELECT
be.alerttime,
Nature                  = ‘Blocked’,
waitresource    = isnull(d.c.value(‘@waitresource ‘,’varchar(100)’),”),
spid = isnull(d.c.value(‘@spid ‘,’varchar(100)’),”),
waittime          = isnull(d.c.value(‘@waittime’,’varchar(100)’),”),
transactionname = isnull(d.c.value(‘@transactionname’,’varchar(100)’),”),
lockMode          = isnull(d.c.value(‘@lockMode’,’varchar(100)’),”),
status                  = isnull(d.c.value(‘@status’,’varchar(100)’),”),
clientapp         = isnull(d.c.value(‘@clientapp’,’varchar(100)’),”),
hostname          = isnull(d.c.value(‘@hostname’,’varchar(100)’),”),
loginname         = isnull(d.c.value(‘@loginname’,’varchar(100)’),”),
currentdb         = isnull(db_name(d.c.value(‘@currentdb’,’varchar(100)’)),”),
inputbuf          = isnull(d.c.value(‘inputbuf[1]’,’varchar(1000)’),”),
spname          = isnull((select object_name(objectid) from sys.dm_exec_sql_text(d.c.value(‘@sqlhandle’,’varbinary(max)’))),’Adhoc’)
FROM msdb..blockedevents be
cross apply blockedreport.nodes(‘TextData/blocked-process-report/blocked-process/process’) d(c)
–Blocking process details

INSERT INTO #BlockingDetails
SELECT
be.alerttime,
Nature                  = ‘BlockedBy’,
waitresource    = ”,
spid = isnull(d.c.value(‘@spid ‘,’varchar(100)’),”),
waittime          = ”,
transactionname = ”,
lockMode          = ”,
status                  = isnull(d.c.value(‘@status’,’varchar(100)’),”),
clientapp         = isnull(d.c.value(‘@clientapp’,’varchar(100)’),”),
hostname          = isnull(d.c.value(‘@hostname’,’varchar(100)’),”),
loginname         = isnull(d.c.value(‘@loginname’,’varchar(100)’),”),
currentdb         = isnull(db_name(d.c.value(‘@currentdb’,’varchar(100)’)),”),
inputbuf          = isnull(d.c.value(‘inputbuf[1]’,’varchar(1000)’),”),
spname          = isnull((select object_name(objectid) from sys.dm_exec_sql_text(d.c.value(‘@sqlhandle’,’varbinary(max)’))),’Adhoc’)
FROM
msdb..blockedevents be
cross apply blockedreport.nodes(‘TextData/blocked-process-report/blocking-process/process’) d(c)

select * from #BlockingDetails

Advertisements

3 thoughts on “Analyzing blocked process report XML

  1. An agent job and alert needs to be setup through SQL Agent to capture blocking alerts.

    USE [msdb]
    GO

    CREATE TABLE [dbo].[BlockedEvents](
    [Event_id] [int] IDENTITY(1,1) NOT NULL,
    [AlertTime] [datetime] NULL,
    [BlockedReport] [xml] NULL,
    [SPID] [int] NULL,
    PRIMARY KEY CLUSTERED
    (
    [Event_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    USE [msdb]
    GO

    /****** Object: Job [Blocking_Alert] Script Date: 07/29/2013 04:35:18 ******/
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 07/29/2013 04:35:18 ******/
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
    IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Blocking_Alert’,
    @enabled=1,
    @notify_level_eventlog=0,
    @notify_level_email=0,
    @notify_level_netsend=0,
    @notify_level_page=0,
    @delete_level=0,
    @description=N’No description available.’,
    @category_name=N'[Uncategorized (Local)]’,
    @owner_login_name=N’***********’, @job_id = @jobId OUTPUT
    IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
    /****** Object: Step [Alert for Blocking] Script Date: 07/29/2013 04:35:18 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Alert for Blocking’,
    @step_id=1,
    @cmdexec_success_code=0,
    @on_success_action=1,
    @on_success_step_id=0,
    @on_fail_action=2,
    @on_fail_step_id=0,
    @retry_attempts=0,
    @retry_interval=0,
    @os_run_priority=0, @subsystem=N’TSQL’,
    @command=N’
    /*

    Use below script in the step of job DBA_BlockedAlert
    */

    SET QUOTED_IDENTIFIER ON

    –Inserting into a table for further reference
    INSERT INTO msdb.dbo.BlockedEvents
    (AlertTime, BlockedReport)
    VALUES (getdate(), N”$(ESCAPE_SQUOTE(WMI(TextData)))”)

    –Updating the SPID column
    UPDATE B
    SET B.SPID = B.BlockedReport.value(”(/TextData/blocked-process-report/blocking-process/process/@spid)[1]”,”int”)
    FROM msdb.dbo.BlockedEvents B
    where B.Event_id = SCOPE_IDENTITY()

    ‘,
    @database_name=N’master’,
    @flags=0
    IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
    IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:

    GO

    EXEC msdb.dbo.sp_add_alert @name=N’Find blocking’,
    @message_id=0,
    @severity=0,
    @enabled=1,
    @delay_between_responses=0,
    @include_event_description_in=0,
    @category_name=N'[Uncategorized]’,
    @wmi_namespace=N’\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER’,
    @wmi_query=N’SELECT * FROM BLOCKED_PROCESS_REPORT’
    GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s