none
Creating an Alert for Blocked Process Threshold

    Question

  • According to BOL you can configure an Alert to notify you when the blocked process threshold has been exceeded:

     

    SQL Server 2005 Books Online

    blocked process threshold Option

    Use the blocked process threshold option to specify the threshold, in seconds, at which blocked process reports are generated. The threshold can be set from 0 to 86,400. By default, no blocked process reports are produced. This event is not generated for system tasks or for tasks that are waiting on resources that do not generate detectable deadlocks. For more information about deadlock detection, see Detecting and Ending Deadlocks.

    You can define an alert to be executed when this event is generated.
    So for example, you can choose to page the administrator to take appropriate action to handle the blocking situation.

     

    Can someone provide some direction on exactly how this is done?  Does it require a Service Broker and queue?

     

    Wednesday, July 18, 2007 8:09 PM

All replies

  • You can go to SQL Server Agent, right click on the Alerts folder and select "New Alert...".

    Change Type to "SQL Server performance condition alert".

    Under Object, select General Statistics.

    Under Counter, select Processes blocked.

    Use the Alert if counter fields to define how many blocked processes are required to trigger the alert. 

    Under the Response tab, set up what you want the alert to do when it is triggered.

    HTH

    Friday, January 20, 2012 3:03 PM
  • Here is the article :- http://sql-blogs.com/2012/01/04/configure-sql-agent-alerts-to-notify-the-performance-issues/ which will help to implement the alerts using SQL agent.

    For the counter "processes blocked" : - it tells us the no. of blocked sessions as far as I remember.

    Please check this article and see if it adds some value: - http://blogs.technet.com/b/rob/archive/2008/05/26/detecting-sql-server-2005-blocking.aspx

     


    Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
    |Team Blog:- Team Blog
    Friday, January 20, 2012 5:21 PM
  • Hi DTiption, 

    You can create a alert and notification by using the following script, you need to create a operator, for notification, if you don't have one replace N'DBA' with the name of that operator.  

    This script will send and email to operator if blocking process counter rise above 0

    USE [msdb]

     

    GO

    EXEC msdb.dbo.sp_add_alert @name=N'Blocking Process', 

     @enabled=1, 

     @delay_between_responses=0, 

     @include_event_description_in=0, 

    @performance_condition=N'SQLServer:General Statistics|Processes blocked||>|0', 

    @job_id=N'00000000-0000-0000-0000-000000000000'

    GO

    EXEC msdb.dbo.sp_add_notification @alert_name=N'Blocking Process', @operator_name=N'DBA', @notification_method = 1

    GO

     


    Thanks, Harpreet
    • Edited by Harpreets.Singh Friday, January 20, 2012 5:34 PM Change from named instance to default instance
    Friday, January 20, 2012 5:32 PM
  • Hi DTipton,

    Starting from SQL 2005 onwards we can use WMI based alerting to capture an alert, process it and send an intimation. In below script we will:

    1) Create a table BLOCKED_PROCESS_REPORT in MSDB daabase.

    2) Create a WMI based ALERT to capture BLOCKED PROCESS based on THREASHOLD value (in this example its set to 20 seconds).

    3) Create a SQL AGENT JOB which will execute once this ALERT got fired. This job will write information to a table and then send an email alert.

    4) It will send an email which will provide BLOCKING GRAPH like below which you can monitor later on.

    <TextData><blocked-process-report>&#x0A; <blocked-process>&#x0A;  <process id="process4de0550" taskpriority="0" logused="0" waitresource="KEY: 11:415237773918208 (010086470766)" waittime="118166" ownerId="89720" transactionname="SELECT" lasttranstarted="2012-01-21T15:35:33.793" XDES="0x9a5bde8" lockMode="S" schedulerid="2" kpid="8836" status="suspended" spid="55" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2012-01-21T15:35:33.793" lastbatchcompleted="2012-01-21T15:34:58.530" lastattention="2012-01-21T15:28:16.497" clientapp="Microsoft SQL Server Management Studio - Query" hostname="GURPSETH" hostpid="10104" loginname="GURPSETH\gursethi" isolationlevel="read committed (2)" xactid="89720" currentdb="11" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">&#x0A;   <executionStack>&#x0A;    <frame line="1" sqlhandle="0x02000000cdfba339a22bb1b2dd1d40543e0e8a4b2cded8ba"></frame>&#x0A;   </executionStack>&#x0A;   <inputbuf>&#x0A;select * from categories   </inputbuf>&#x0A;  </process>&#x0A; </blocked-process>&#x0A; <blocking-process>&#x0A;  <process status="sleeping" spid="57" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2012-01-21T15:35:32.107" lastbatchcompleted="2012-01-21T15:35:32.107" clientapp="Microsoft SQL Server Management Studio - Query" hostname="GURPSETH" hostpid="10104" loginname="GURPSETH\gursethi" isolationlevel="read committed (2)" xactid="89718" currentdb="11" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">&#x0A;   <executionStack></executionStack>&#x0A;   <inputbuf>&#x0A;begin tran&#x0A;update Categories set Description=&apos;Soft drinks, coffees, teas, beers and ales&apos; where CategoryID=1&#x0A;   </inputbuf>&#x0A;  </process>&#x0A; </blocking-process>&#x0A;</blocked-process-report>&#x0A;</TextData>

    Hope this will help you.
     

    /* Step 1: creating the table to capture the Event information */
    
    USE    MSDB
    GO
    
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BLOCKED_PROCESS_REPORT]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE [dbo].[BLOCKED_PROCESS_REPORT]
    GO
    
    CREATE TABLE [dbo].[BLOCKED_PROCESS_REPORT] (
    [PostTime] [datetime] NOT NULL ,
    [SQLInstance] varchar(50),
    [Databaseid] int,
    [computerName] Varchar(50),
    [SessionLoginName] Varchar(50),
    [SPID] int,
    [TransactionID] int,
    [EventSequence] int,
    [objectID] int,
    [IndexID] int,
    [TextData] nvarchar(4000) ,
    [duration] int,
    [RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
    [Flag] [int] NOT NULL CONSTRAINT [DF_BLOCKED_PROCESS_REPORT_Flag]  DEFAULT ((0))
    ) ON [PRIMARY]
    GO
    
    CREATE INDEX [BLOCKED_PROCESS_REPORT_IDX01] ON [dbo].[BLOCKED_PROCESS_REPORT]([Posttime]) WITH FILLFACTOR = 100 ON [PRIMARY]
    GO
    
    /*Step 2 : Creating the Job that will enter values into the Deadlockevents table created above*/
    /*Service account and sql operator option are optional*/
    
    USE [msdb]
    GO
    
    IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture BLOCKED_PROCESS_REPORT Event')
    EXEC msdb.dbo.sp_delete_job @job_name = N'Capture BLOCKED_PROCESS_REPORT Event', @delete_unused_schedule=1
    
    GO
    --DECLARE @ServiceAccount varchar(128)
    --SET @ServiceAccount = N'<job_owner_account>'
    --DECLARE @SQLOperator varchar(128)
    --SET @SQLOperator = N'<sql_agent_operator>'
    
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    
    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'Capture BLOCKED_PROCESS_REPORT Event', 
    @enabled=1, 
    @notify_level_eventlog=2, 
    @notify_level_email=3, 
    @notify_level_netsend=0, 
    @notify_level_page=0, 
    @delete_level=0, 
    @description=N'Job for responding to blocking events', 
    @category_name=N'[Uncategorized (Local)]', 
    --@owner_login_name=@ServiceAccount, 
    --@notify_email_operator_name=@SQLOperator, 
    @job_id = @jobId OUTPUT
    
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    
    /*Step 3: Insert graph into LogEvents*/
    
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert data into LogEvents', 
    @step_id=1, 
    @cmdexec_success_code=0, 
    @on_success_action=3, 
    @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'
    
    INSERT INTO BLOCKED_PROCESS_REPORT (
    [PostTime] ,
    [SQLInstance] ,
    [Databaseid] ,
    [computerName],
    [SessionLoginName],
    [SPID] ,
    [TransactionID] ,
    [EventSequence] ,
    [objectID] ,
    [IndexID] ,
    [TextData],
    [duration]
    )
    
    VALUES (
    GETDATE(), 
    N''$(ESCAPE_NONE(WMI(SQLInstance)))'',
    N''$(ESCAPE_NONE(WMI(Databaseid)))'',
    N''$(ESCAPE_NONE(WMI(ComputerName)))'',
    N''$(ESCAPE_NONE(WMI(SessionLoginname)))'',
    N''$(ESCAPE_NONE(WMI(SPID)))'',
    N''$(ESCAPE_NONE(WMI(TransactionID)))'',
    N''$(ESCAPE_NONE(WMI(EventSequence)))'',
    N''$(ESCAPE_NONE(WMI(objectid)))'',
    N''$(ESCAPE_NONE(WMI(indexid)))'',
    N''$(ESCAPE_SQUOTE(WMI(Textdata)))'',
    N''$(ESCAPE_NONE(WMI(Duration)))''
    )', 
    @database_name=N'  MSDB ', 
    @flags=0
    
    
    /*Step 4: Adding the job step 2 to execute the SPs for sending mail and changing the flag for the mail sent */
    declare @command1 nvarchar (200)
    set @command1='use    MSDB   ' +'; exec BLOCKED_PROCESS_REPORT_rpt;exec [dbo].[flag_BLOCKED_PROCESS_REPORT];'
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sending mail and changing the flag value', 
    @step_id=2, 
    @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=@command1
    
    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
    
    /*Creating the alert and associating it with the Job to be fired */
    
    USE [msdb]
    GO
    
    IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to BLOCKED_PROCESS_REPORT')
    EXEC msdb.dbo.sp_delete_alert @name=N'Respond to BLOCKED_PROCESS_REPORT'
    
    GO
    declare @count int
    declare @instance nvarchar(50)
    declare @server nvarchar(50)
    DECLARE @server_namespace varchar(255)
    select @instance=convert(nvarchar(50),(SERVERPROPERTY('instancename')))
    select @server=convert(nvarchar(50),(SERVERPROPERTY('ComputerNamePhysicalNetBIOS')))
    IF (@instance is not NULL)
    	SET @server_namespace = N'\\'+@server+'\root\Microsoft\SqlServer\ServerEvents\' + @instance
    ELSE
    	SET @server_namespace = N'\\'+@server+'\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'
    
    
    EXEC msdb.dbo.sp_add_alert @name=N'Respond to BLOCKED_PROCESS_REPORT',
    @enabled=1, 
    @notification_message=N'Your Message', 
    @wmi_namespace=@server_namespace, 
    @wmi_query=N'SELECT * FROM BLOCKED_PROCESS_REPORT Where Duration >=20000', 
    @job_name='Capture BLOCKED_PROCESS_REPORT Event' ;
    
    --EXEC msdb.dbo.sp_add_notification @alert_name=N'Respond to BLOCKED_PROCESS_REPORT', @operator_name=N'Test', @notification_method = 1
    --GO
    
    
    
     go
     
     /* Step 6: Create a stored proc for capturing blocking graph */ 
     use msdb 
     go
     
     IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[blk_text]') AND OBJECTPROPERTY(id, N'Isprocedure') = 1)
     DROP proc  [dbo].[blk_text]
     go
     create proc blk_text as
     print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
     print 'Following is the blocking graph'
     print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
     select textdata from msdb..BLOCKED_PROCESS_REPORT where flag=0
     print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
     print 'END of blocking graph'
     print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
     go
     
     /* Step 7: Create a stored proc for capturing Last Stats update and Index fragmentation of the tables involved in blocking */ 
     
     /*set nocount on,include column headers in the resultset */
     use msdb 
     
     IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Stats_index]') AND OBJECTPROPERTY(id, N'Isprocedure') = 1)
     DROP proc  [dbo].[Stats_index]
     go
     Create procedure Stats_index as 
     set nocount on   
     print 'Following are the Statistics and Index fragmentation details of the object(s) involved in Blocking'
     print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
     IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[stats_state]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
     DROP TABLE msdb.[dbo].[stats_state]
     create table msdb..Stats_state (spid bigint,dbid bigint,objid bigint,indid bigint,type varchar (100),resource varchar(200),mode varchar(100),status varchar(100))
     insert into msdb..Stats_state exec sp_lock 
     
     declare contig_Cursor CURSOR FOR select distinct j.objid from msdb..stats_state i inner join msdb..stats_state j on i.resource = j.resource where i.resource =j.resource and i.status ='wait'
     FOR READ ONLY
     open contig_Cursor
     declare @spid bigint
     declare @i_buff_string char(30) 
     declare @obj_id bigint
     declare @obj_name nvarchar(50)
     declare @obj_name_temp nvarchar(50)
     declare @syntax nvarchar(100)
     declare @syntax1 nvarchar(100)
     declare @dbid int;
     declare @dbname nvarchar(3000);
     select distinct @dbid = j.dbid from msdb..stats_state i inner join msdb..stats_state j on i.resource = j.resource where i.resource =j.resource and i.status ='wait'
     select @dbname=db_name(@dbid)
     
     fetch next from contig_Cursor into @obj_id 
     
     while (@@fetch_status <> -1) 
     begin 
     select distinct @dbid= j.dbid from msdb..stats_state i inner join msdb..stats_state j on i.resource = j.resource where i.resource =j.resource and i.status ='wait' and j.objid=@obj_id
     set @dbname = db_name(@dbid)
     select @obj_name= object_name(@obj_id)
     
     print 'Show conting output'
     Print '~~~~~~~~~~~~~~~~~~~~'
     SELECT database_id 'dbid',object_id 'objid',index_id 'indid',index_type_desc 'indtype',avg_fragmentation_in_percent '% frag',avg_page_space_used_in_percent 'page_full%',page_count 'page_cnt' FROM sys.dm_db_index_physical_stats(@dbid, @obj_id, NULL, NULL , 'LIMITED');
     
     print' 
     
     '
     
     print 'Last Stats update for the above object was :'
     Print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
     set @dbname='use '+@dbname +';
     
     declare contig_2_Cursor CURSOR FOR select distinct j.objid from msdb..stats_state i inner join msdb..stats_state j on i.resource = j.resource where i.resource =j.resource and i.status ='+'''wait''
     FOR READ ONLY
     open contig_2_Cursor
     declare @obj_id int;
     declare @obj_name nvarchar(300);
     
     fetch next from contig_2_Cursor into @obj_id 
     while (@@fetch_status <> -1) 
     begin 
     
     select distinct @obj_id = j.objid from msdb..stats_state i 
     	inner join msdb..stats_state j on i.resource = j.resource 
     	where i.resource =j.resource and i.status ='+'''wait;''' +'
     select @obj_name =object_name(@obj_id);select "Index Name" = i.name, "Statistics Date" = STATS_DATE(i.object_id, i.index_id)
     FROM sys.objects o JOIN sys.indexes i ON o.name = ' +'@obj_name'+ ' AND o.object_id = i.object_id
     where i.name is not null;
     
     fetch next from contig_2_Cursor into @obj_id
     end 
     close contig_2_Cursor
     deallocate contig_2_Cursor
     '
     --print @dbname
     exec(@dbname)
     
     fetch next from contig_Cursor into @obj_id
     end 
     close contig_Cursor
     deallocate contig_Cursor
     go
     
     /*Step 9: Creating SP to capture SP_WHO , Head blocker and sp_lock information.Also adding above SP here to be executed  */
     use msdb 
     go
     IF OBJECT_ID('blk_information','P') IS NOT NULL DROP PROC dbo.blk_information
     GO
     CREATE PROC dbo.blk_information 
     AS
     print ' blocking List:'
     Print '~~~~~~~~~~~~~~~~'
     Select SPID,Blocked,Waittype,Waittime,lastwaittype,program_name,waitresource,dbid,last_batch,open_tran from sys.sysprocesses where blocked <>0
     PRINT ''
     
     Print ' Head blockers List'
     Print '~~~~~~~~~~~~~~~~~~~~'
     select spid as [Blocking spid],loginame,hostname,program_name as progname,cmd,status,physical_io,waittype
     from master.dbo.sysprocesses 
     where spid in (select blocked from master.dbo.sysprocesses)
     and blocked=0
     PRINT ''
     
     declare @spid bigint
     declare @i_buff_string char(30) 
     set nocount on   
     declare bufCursor CURSOR FOR SELECT spid from master.dbo.sysprocesses where blocked <> 0 OR spid in (select blocked from master.dbo.sysprocesses)
     FOR READ ONLY
     open bufCursor
     fetch next from bufCursor into @spid 
     while (@@fetch_status <> -1) 
     begin 
        Print   'SP_WHO'
        select '~~~~~~~~' 
        exec sp_who @spid 
        PRINT ''
         
        PRINT  'SP_LOCK' 
        select '~~~~~~~~'  
        exec sp_lock @spid 
        PRINT ''
        
     fetch next from bufCursor into @spid 
     end 
     close bufCursor
     deallocate bufCursor
     go
      
    
    /* Step 5: Create a stored proc for sending the deadlock information as .txt file */
    USE    msdb  
    GO 
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.BLOCKED_PROCESS_REPORT_rpt') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP proc dbo.BLOCKED_PROCESS_REPORT_rpt
    go
    Create proc [dbo].[BLOCKED_PROCESS_REPORT_rpt] 
    as
    DECLARE @Server_name nvarchar(30)
    DECLARE @instance_name nvarchar(30)
    DECLARE @SQL varchar(2000)
    DECLARE @date varchar (2000)
    DECLARE @File varchar(1000)
    DECLARE @subject1 nvarchar (1000)
    select @date= convert(varchar,GETDATE())
    --select @date= datepart(day,GETDATE())
    SET @SQL='msdb'
    SET @SQL = 'print ''
    
    
    '' use  msdb ;set nocount on; exec blk_text;exec blk_information;Exec Stats_index'
    --SET @SQL = 'select * from BLOCKED_PROCESS_REPORT where flag = 0'
    SET @File = 'BLOCKED PROCESS Report'+@date+'.txt'
    Select @server_name=host_name()
    Select @instance_name=@@servicename
    set @subject1='Blocking report for ->'+'Server: '+ @server_name+' and Instance: '+@instance_name
    
     
    EXECUTE msdb.dbo.sp_send_dbmail
    @profile_name = 'MAILPROFILE',
    @recipients = 'xxxx.yyyy@yyyy.com',
    @subject = @subject1,
    @body = '***URGENT***Attached please find the BLOCKED PROCESS report',
    @query =@SQL ,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = @file,
    @query_result_header = 1,
    @query_result_separator = ' ',
    @query_result_no_padding = 1,
    @query_result_width = 32767 
    
    go
    /* Step 6: Changing the flag to 1 so that next time this information is not sent*/ 
    use    msdb  
    go
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.flag_BLOCKED_PROCESS_REPORT') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP proc dbo.flag_BLOCKED_PROCESS_REPORT
    go
    create proc [dbo].[flag_BLOCKED_PROCESS_REPORT] as 
    update    msdb.dbo.BLOCKED_PROCESS_REPORT set flag = 1 where flag = 0
    go
    
    
    
    
    

     


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    Saturday, January 21, 2012 10:13 AM
  • Hello, GURSETHI!

    Thanks for your post. I used it and it's works fine, but i have a doubt.

    I want to generate a graph in xml with TextData column. So i changed the format of TextData column.

    I put [TextData] [xml]  instead of  [TextData] nvarchar(4000)

    So i used a bcp command to put the result of this column in a .xdl archive to see the result in graph.

    But i think that the xml generated is wrong because when i open my xdl i get this error:

    failed to initialize deadlock control

    there is an error in xml document (1,2).
    <blocked-process-report xmlns="> was not expected

    my script to generate the xdl is below:

    "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" BCP "SELECT TOP 1 [TextData].query('/TextData/blocked-process-report') FROM [MSDB].[dbo].[BLOCKED_PROCESS_REPORT] ORDER BY PostTime DESC" queryout blocks.xdl -T -c -q

    Do you think you can help me?

    Tuesday, July 03, 2012 8:06 PM