Product has been added to your cart.

Finding Blocked Processes and Deadlocks using SQL Server Extended Events

Jeremiah Peschka
Locking, Blocking, and Isolation Levels, SQL Server
58 Comments

A lot of folks would have you think that Extended Events need to be complicated and involve copious amounts of XML shredding and throwing things across the office. I’m here to tell you that it doesn’t have to be so bad.

Collecting Blocked Process Reports and Deadlocks Using Extended Events

When you want to find blocking, you probably turn to the blocked process report. You mess around with profiler on your SQL Server 2012 box. You probably feel a little bit dirty for clunking around in that old interface, but it gets the job done.

There’s a better way… Well, there is at least a less awful way: Extended Events.

With that, you’ve created an Extended Events session to grab blocked processes and deadlocks. Why both? The blocked process report makes use of the deadlock detector. Since large amounts of blocking are frequently synonymous with deadlocking, it makes sense to grab both at the same time. There are a few other things we’ll need to do to make sure you can collect blocked processes:

At this point, you’ll be collecting the blocked process report with Extended Events. There’s no profiler session to set up, just start and stop the Extended Event session at your leisure.

Reading the Block Process Report from Extended Events

We’re saving the blocked process report to disk using Extended Events. Now what?

We need to get that blocked process data out of the Extended Events files and somewhere that we can better analyze it.

In this query, you read from an Extended Events session that’s being saved to disk and perform XML shredding to get client information. It isn’t a pretty query, but it does the job very well.

Viewing the Extended Events Deadlock Graphs

Extended Events deadlock graphs use a slightly different XML schema than what SSMS expects. You should see an error along the lines of “There is an error in XML document”. For folks using SQL Server 2012 and earlier, you can either parse the XML by hand or use SQL Sentry Plan Explorer.

Viewing the Extended Events Blocked Process Report

But what about the blocked process report? After all, your users are complaining about blocking, right?

Michael J. Swart has created tools to view the blocked process report. It’d be awesome if you could use it, but Michael’s blocked process report viewer uses the output of a server side trace to read blocking information. These Extended Events files are different enough that you can’t use them outright. You can, however, create a table that will let you use the blocked process report viewer:

While you still have to read the XML yourself, this will give you a view into how deep the blocking hierarchies can go. Collecting this data with Extended Events mean that you won’t have to sit at your desk, running queries, and waiting for blocking occur.

Extended Events – Not That Hard

Extended Events aren’t difficult to use. They provide a wealth of information about SQL Server and make it easier to collect information from complex or difficult to diagnose scenarios. You really can collect as much or as little information as you want from SQL Server. When you get started, the vast majority of your work will be spent either looking up Extended Events to use or formatting the output of the queries into something meaningful.

Previous Post
T-SQL Tuesday: Stop Tuning with Wait Stats Percentages
Next Post
When Do You Fire the DBA?

58 Comments. Leave new

  • Michael J Swart
    March 12, 2014 7:59 am

    Thanks for plugging the blocked process report viewer.
    I haven’t revisited the tool in a while. Extended events for blocked processes didn’t exist when I wrote the blocked process report viewer. Maybe it’s time to revisit it. If I do, I’ll let you know.

    Reply
  • Elijah W. Gagne
    March 12, 2014 8:00 pm

    Minor typo, you have:

    EXEC sp_configure ‘blocked process report’, ‘5’;
    RECONFIGURE
    GO

    But it should be:

    EXEC sp_configure ‘blocked_process’, ‘5’;
    RECONFIGURE
    GO

    Regards,
    EWG

    Reply
  • Jaime Martinez Lafargue (@jaimeml)
    March 13, 2014 10:09 am

    Hello Jeremiah,

    Really useful post. I am testing it right now and will use it in a customer, who’s complaining about performance problems.

    Just a couple of minor things:

    SQL throws an error when starting the event session if the path selected for the event files does not exist. The “problem” is that you don’t see any error when creating the event session, but as I said, when starting it:

    Msg 25602, Level 17, State 22, Line 1
    The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”, encountered a configuration error during initialization. Object cannot be added to the event session. The operating system returned error 3: ‘The system cannot find the path specified.
    ‘ while creating the file ‘c:\myFoñder\XEventSessions\blocked_process_0_130391967440060000.xel’.

    The second question is that I think that you should add this

    sp_configure ‘show advanced options’, 1 ;
    GO
    RECONFIGURE ;
    GO

    just before

    EXEC sp_configure ‘blocked process threshold’, ‘5’;
    RECONFIGURE
    GO

    BTW, it works for me with this literal instead of ‘blocked_process’, in spite of what Elijah pointed out.

    Kind regards,

    Jaime

    Reply
    • Jeremiah Peschka
      March 13, 2014 10:26 am

      Thanks for pointing that out! I’ve added the code to show advanced options into the example. I have to admit – it’s pretty rare to encounter a SQL Server that isn’t using that setting any more. I’ll also admit to never having seen SQL Server use an underscore in an sp_configure setting. Something tells me that’s from the dark ages of SQL Server, or else it exists to give warm fuzzies to the Oracle crowd.

      Reply
  • murali
    March 17, 2014 7:23 am

    Hi

    Thanks for the post. Just want to make sure, is this valid for SQL SERVER 2008R2 SP2 as well.

    Thanks
    Murali

    Reply
    • Brent Ozar
      March 17, 2014 7:24 am

      Murali – when in doubt, try it out. 😉 It’ll take less time to give it a shot on your system than it would to post this comment, believe it or not. Enjoy the journey!

      Reply
    • Jaime Martinez Lafargue (@jaimeml)
      March 17, 2014 7:42 am

      Hi Murali,

      I can tell you that the event sqlserver.blocked_process_report does not exist in SQL Server 2008 (R2).
      You can check for available events with this script:

      select p.name as package, xo.name as object_name, xo.description, xo.object_type
      from sys.dm_xe_objects xo
      join sys.dm_xe_packages p
      on xo.package_guid = p.guid

      Also the event action name sqlserver.database_name produces the following error:
      Msg 25623, Level 16, State 3, Line 1
      The event action name, “sqlserver.database_name”, is invalid, or the object could not be found

      Regards,

      Jaime

      Reply
  • David Parsons
    April 11, 2014 10:50 am

    Great article, thanks. It’s got me started on using extended events to track down some issues that one of our clients is having with performance on their system.

    Can you recommend any resources for further reading?

    I am trying to work out how, using the sqlserver.lock_released event, I can find the table which a page or extent level lock relates. I’d have thought it would have been straightforward, and something that others would have done before, but can’t find information.

    thanks

    Reply
    • Jeremiah Peschka
      April 11, 2014 11:07 am

      Hi David,

      I haven’t checked into this myself, but there’s some great information in Lock:Released documentation as well as a few tutorials over on John Huag’s blog here and here.

      Basically, object_id is your… object_id, res0 is page and res1 is the file number.

      I’d be careful setting up extended events sessions capturing locks. You could also use sp_BlitzIndex and look at the aggressive indexes or even query sys.dm_db_index_operational_stats and get two samples to help you figure out which index is having heavy locking.

      Hope that helps!

      Reply
      • David Parsons
        April 14, 2014 3:52 am

        Hi Jeremiah,

        Thanks for the advice. sys.dm_db_index_operational_stats gives me a lot of what I need.

        I also think that I can use the TransactionID field to match page level locks with the Intent, IS, mode lock taken on the table. That gives me a clearer picture of what’s happening.

        Cheers

        David

        Reply
  • van
    November 24, 2014 3:12 pm

    Hi Brent & Jeremiah,

    I am looking for an option to change the Extended event log files location. But did not see any option to change. There is anyway i can change the location.

    Thanks
    Van

    Reply
    • Jeremiah Peschka
      November 24, 2014 6:04 pm

      I’m not entirely sure what you mean by “Extended Event log files location”.

      The system_health session is always running and it is an in memory only Extended Event session.

      For any other Extended Event session, you can specify the location using (SET filename = N'c:\temp\XEventSessions\blocked_process.xel'). Was that what you meant?

      Reply
  • sk
    December 3, 2014 4:00 am

    MSG 25602 you will probably get it if you do have clustering in your environment and you created the path on the node where the instance is not running on. Sln : check on which node the instance is hosted and create it the path on that node. and note that the blocked_process.xel and xem are folders not files…

    hope that will help..
    Thanks

    SK.

    Reply
  • Timothy Yzermans
    December 9, 2014 12:01 pm

    Hi Brent,

    Really interesting post!

    When I wanted to run this on our server I received the following error when executing the first script: Msg 25641, Level 16, State 0, Line 1
    For target, “package0.asynchronous_file_target”, the parameter “filename” passed is invalid. Target parameter at index 0 is invalid

    Any ideas?

    Thx!
    Timothy

    Reply
  • Jagan Kondapalli
    January 12, 2015 1:52 pm

    I would like to create this on my system for queries running longer than 30 Seconds. Do you recommend running Extended Events for sqlserver.sql_statement_starting and sqlserver.sql_statement_Completed continuously in an OLTP system>

    Reply
  • Mal D
    June 18, 2015 3:24 pm

    Thanks Ladies and Gents
    Especially Jeremiah, you all rock. I see the use of extended events likened to the understanding of powershell and how it pertains to being a better DBA. If you are not investing time in learning about these two technologies then you are just coasting. These two are rock star material.
    Thanks again for aiding my learning.

    Reply
  • ponraj
    November 4, 2015 9:11 am

    hi,

    how we can i check the bad system health and blocking by using system_health Extended event session?

    could please help me out on this

    Reply
  • ponraj
    November 4, 2015 9:20 am

    Thank you brent. i will do a check on that

    Reply
  • Tony Fountain
    February 17, 2016 2:19 pm

    This script is the bomb diggity. Yes… I said bomb diggity. The only thing that had to be changed was the location of the files – and it works like a charm. I especially like the fact it couples blocking in with the deadlocks.

    Reply
  • willemwout
    March 21, 2016 4:07 pm

    I got this working on 2012 when i changed the last line

    EXEC sp_blocked_process_report_viewer @Trace=’bpr’, @Type=’TABLE’;

    into

    EXEC sp_blocked_process_report_viewer @SOURCE=’bpr’, @Type=’TABLE’;

    otherwise, with an unmodified cut& paste ,i got the error:

    Msg 201, Level 16, State 4, Procedure sp_blocked_process_report_viewer, Line 6
    Procedure or function ‘sp_blocked_process_report_viewer’ expects parameter ‘@Source’, which was not supplied.

    Reply
  • WW
    March 22, 2016 4:26 am

    first sql text gave me this on a 2008:

    Msg 25623, Level 16, State 1, Line 1
    The event name, “sqlserver.blocked_process_report”, is invalid, or the object could not be found

    select @@version

    Microsoft SQL Server 2008 R2 (SP3) – 10.50.6220.0 (X64)

    Any idea how to fix this error?

    Reply
  • ww
    March 23, 2016 9:12 am

    Hello Michael,
    Thank you very much for your prompt reply. 🙂

    Reply
  • Clayton
    May 2, 2016 6:42 am

    Jeremiah,

    This post and one by Kendra were most helpful! In order to drill deeper into an ongoing deadlock problem, I took your query from this post and reworked it to provide more detail from the deadlock_graph and blocked_process_report XML fields.

    The primary change is an addition of two columns, [Blocked/Deadlocked] and [Blocker/Deadlocked], that are derived from the XML fields. In the case of the deadlock_graph, the blocker can be incorrect if there are multiple victims, and the selection is relying on pulling data from the second ‘process’ in the ‘process-list’.

    For the Blocked Processes, there are a lot of records that have inputbuf values like ‘Proc [Database Id = 7 Object Id = 1454628225]’. For these I added outer apply ‘functions’ to break out the ID values so that they could be returned as meaningful text using the Object_Name() functions.

    It is not perfect, but provides more info quickly, which I use in a Power Pivot workbook to drill into problems and look for patterns.

    Here is the script:

    WITH events_cte
    AS (SELECT [xevents].[event_data]
    , DATEADD([mi], DATEDIFF([mi], GETUTCDATE(), CURRENT_TIMESTAMP)
    , [xevents].[event_data].value(‘(event/@timestamp)[1]’, ‘datetime2’)) AS [event time]
    , [xevents].[event_data].value(‘(event/action[@name=”client_app_name”]/value)[1]’, ‘nvarchar(128)’) AS [client app name]
    , [xevents].[event_data].value(‘(event/action[@name=”client_hostname”]/value)[1]’, ‘nvarchar(max)’) AS [client host name]
    , [xevents].[event_data].value(‘(event[@name=”blocked_process_report”]/data[@name=”database_name”]/value)[1]’, ‘nvarchar(max)’) AS [database name]
    , [xevents].[event_data].value(‘(event[@name=”blocked_process_report”]/data[@name=”database_id”]/value)[1]’, ‘int’) AS [database_id]
    , [xevents].[event_data].value(‘(event[@name=”blocked_process_report”]/data[@name=”object_id”]/value)[1]’, ‘int’) AS [object_id]
    , [xevents].[event_data].value(‘(event[@name=”blocked_process_report”]/data[@name=”index_id”]/value)[1]’, ‘int’) AS [index_id]
    , [xevents].[event_data].value(‘(event[@name=”blocked_process_report”]/data[@name=”duration”]/value)[1]’, ‘bigint’)/1000 AS [duration (ms)]
    , [xevents].[event_data].value(‘(event[@name=”blocked_process_report”]/data[@name=”lock_mode”]/text)[1]’, ‘varchar’) AS [lock_mode]
    , [xevents].[event_data].value(‘(event[@name=”blocked_process_report”]/data[@name=”login_sid”]/value)[1]’, ‘int’) AS [login_sid]
    , [xevents].[event_data].[query](‘(event[@name=”blocked_process_report”]/data[@name=”blocked_process”]/value/blocked-process-report)[1]’) AS [blocked_process_report]
    , [xevents].[event_data].[query](‘(event/data[@name=”xml_report”]/value/deadlock)[1]’) AS deadlock_graph
    FROM [sys].[fn_xe_file_target_read_file](‘C:\temp\XEventSessions\blocked_process*.xel’, ‘C:\temp\XEventSessions\blocked_process*.xem’, NULL, NULL)
    CROSS APPLY
    (SELECT CAST([event_data] AS XML) AS [event_data] ) AS [xevents]
    )

    SELECT CASE
    WHEN [blocked_process_report].value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN ‘Deadlock’
    ELSE ‘Blocked Process’
    END AS [ReportType]
    , [event time]
    , CASE [client app name]
    WHEN ”
    THEN ‘ — N/A — ‘
    ELSE [client app name]
    END AS [client app _name]
    , CASE [client host name]
    WHEN ”
    THEN ‘ — N/A — ‘
    ELSE [client host name]
    END AS [client host name]
    , [database name]
    , COALESCE(OBJECT_SCHEMA_NAME([object_id], [database_id]), ‘ — N/A — ‘) AS [schema]
    , COALESCE(OBJECT_NAME([object_id], [database_id]), ‘ — N/A — ‘) AS [table]
    , [index_id]
    , [duration (ms)]
    , [lock_mode]
    , COALESCE(SUSER_NAME([login_sid]), ‘ — N/A — ‘) AS [username]
    ,case when PATINDEX(‘%PROC [[]Database Id =%’, [Obj].[Blocked] ) = 0
    then [Obj].[Blocked]
    else DB_NAME([Blocked_DB]) + ‘.’ + OBJECT_SCHEMA_NAME([Blocked_Object], [Blocked_DB]) + ‘.’ + OBJECT_NAME([Blocked_Object], [Blocked_DB])
    end as [Blocked/Deadlocked]
    ,case when PATINDEX(‘%PROC [[]Database Id =%’, [Obj].[Blocker] ) = 0
    then [Obj].[Blocker]
    else DB_NAME([Blocker_DB]) + ‘.’ + OBJECT_SCHEMA_NAME([Blocker_Object], [Blocker_DB]) + ‘.’ + OBJECT_NAME([Blocker_Object], [Blocker_DB])
    end as[Blocker/Deadlocked]
    ,CASE WHEN blocked_process_report.value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN deadlock_graph
    ELSE blocked_process_report
    END AS Report
    FROM [events_cte]
    CROSS APPLY
    (SELECT CASE WHEN blocked_process_report.value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN deadlock_graph.value(‘(/deadlock/process-list/process/executionStack/frame/@procname)[1]’,’varchar(max)’)
    ELSE blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)
    END AS [Blocked]
    ,CASE WHEN blocked_process_report.value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN null
    ELSE cast(SUBSTRING(blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)
    , PATINDEX(‘%Database Id =%’, blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)) + 13
    ,(PATINDEX(‘%object Id =%’, blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)) -1)
    – (PATINDEX(‘%Database Id =%’, blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)) + 13)) as int)
    END AS [Blocked_DB]
    ,CASE WHEN blocked_process_report.value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN null
    ELSE CAST(SUBSTRING(blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)
    , PATINDEX(‘%object Id =%’, blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)) + 11
    , (PATINDEX(‘%]%’, blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’))-1)
    – (PATINDEX(‘%object Id =%’, blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)) +10 )) as int)
    END AS [Blocked_Object]
    ,CASE WHEN blocked_process_report.value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN deadlock_graph.value(‘(/deadlock/process-list/process[2]/executionStack/frame/@procname)[1]’,’varchar(max)’)
    ELSE blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)
    END AS [Blocker]
    ,CASE WHEN blocked_process_report.value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN null
    ELSE cast(SUBSTRING(blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)
    , PATINDEX(‘%Database Id =%’, blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)) + 13
    ,(PATINDEX(‘%object Id =%’, blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)) -1)
    – (PATINDEX(‘%Database Id =%’, blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)) + 13)) as int)
    END AS [Blocker_DB]
    ,CASE WHEN blocked_process_report.value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN null
    ELSE CAST(SUBSTRING(blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)
    , PATINDEX(‘%object Id =%’, blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)) + 11
    , (PATINDEX(‘%]%’, blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’))-1)
    – (PATINDEX(‘%object Id =%’, blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)) +10 )) as int)
    END AS [Blocker_Object]
    ) as [Obj]

    — where [event time] > ‘4/27/2016 1:08:10 PM’–‘2016-04-15 19:05:30.9450000’
    ORDER BY [event time] DESC;

    Reply
  • Clayton
    May 2, 2016 6:45 am

    Oops. It was this article by Jes Borland that I was referring to… https://www.brentozar.com/archive/2015/01/query-extended-events-target-xml/

    Reply
  • Mark Holahan
    May 11, 2016 8:58 am

    I have a, perhaps, silly question. It is true that the two tools you endorse: 1) SQL Sentry Plan Explorer and 2) Mr. Swart “blocked process report viewer” replace the query you provided? I’m speaking about the query you provide under the “Reading the Block Process Report from Extended Events” section.

    In other words, you provided the aforementioned query as a starting point, but the two tools, 1 & 2, above, are the best tools to use to analyze the data your extended event session definition captures?

    Reply
    • Brent Ozar
      May 11, 2016 9:29 am

      Mark – to rephrase what you’re saying:

      * You need to gather deadlock data
      * You need to view deadlock data

      The tools used for those two tasks are different.

      Reply
      • Mark Holahan
        May 11, 2016 10:49 am

        Brent,

        Thanks for your reply.

        I’m trying to understand Jeremiah’s article. If I understand this article, he’s saying:
        1) create an extended event to capture block and deadlock–and here’s the code
        2) use can this query to read the data we’re not collecting with our new extended event session. I’m referring to the code that’s below this statement: “We need to get that blocked process data out of the Extended Events files and somewhere that we can better analyze it.”
        3) However, just below this query, Jeremiah then has these two sections:
        Viewing the Extended Events Deadlock Graphs
        Viewing the Extended Events Blocked Process Report
        4) So it appears that the tools he describes in these two sections is a better option to the query given in 2)
        5) Is my understanding correct? I should avail myself to these two tools in lieu of his query in 2)?

        Reply
  • eric81
    June 2, 2016 3:56 pm

    I know SQL Server generates its own xel files which are recycled regularly, but don’t they contain deadlock information?

    Reply
  • tsehaynesh kebede
    March 24, 2017 3:50 pm

    I was able to successfully query the xml file but I can’t seem to be splitting up the blocking sql statement from the blocking sql statement. Can you please advice

    SELECT BLR.event_data.value(‘(@timestamp)[1]’, ‘datetime’) AS [timestamp],
    BLR.event_data.value(‘(//process[@id[//blocked-process/process[1]/@id]]/@hostname)[1]’, ‘nvarchar(max)’) AS Blocked_HostName,
    BLR.event_data.value(‘(//process[@id[//blocked-process/process[1]/@id]]/@clientapp)[1]’, ‘nvarchar(max)’) AS Blocked_AppName,
    BLR.event_data.value(‘(//process[@id[//blocked-process/process[1]/@id]]/@loginname)[1]’, ‘nvarchar(max)’) AS Blocked_LoginName,
    BLR.event_data.value(‘(data[@name=”database_id”]/value)[1]’, ‘int’) AS blocked_db_id,
    BLR.event_data.value(‘(data[@name=”object_id”]/value)[1]’, ‘int’) AS blocked_object_id,
    BLR.event_data.value(‘(data[@name=”index_id”]/value)[1]’, ‘int’) AS blocked_index_id,
    BLR.event_data.value(‘(data[@name=”lock_mode”]/text)[1]’, ‘varchar(25)’) AS lock_mode,
    BLR.event_data.value(‘(//process[@id[//blocked-process/process[1]/@id]]/@waittime)[1]’, ‘nvarchar(max)’) AS waittime_insec,
    BLR.event_data.value(‘(//process[@status[//blocking-process/process[1]/@status]]/@hostname)[1]’, ‘nvarchar(max)’) AS Blocking_Hostname,
    BLR.event_data.value(‘(action[@name=”server_instance_name”]/value)[1]’, ‘varchar(max)’) AS blocking_InstanceName,
    BLR.event_data.value(‘(//process[@status[//blocking-process/process[1]/@status]]/@clientapp)[1]’, ‘nvarchar(max)’)as blocking_AppName,
    BLR.event_data.value(‘(data[@name=”database_name”]/value)[1]’, ‘varchar(25)’) blocking_db_name,
    BLR.event_data.value(‘(//process[@status[//blocking-process/process[1]/@status]]/@loginname)[1]’, ‘nvarchar(max)’) AS blocking_UserName,
    BLR.event_data.value(‘(action[@name=”server_principal_name”]/value)[1]’, ‘varchar(max)’) AS blocking_PrincipalUserName,
    BLR.event_data.value(‘(data[@name=”blocked_process”]/value)[1]’, ‘varchar(max)’) AS blocked_process
    FROM Blocking_Report br
    CROSS APPLY targetdata.nodes(‘//RingBufferTarget/event’) AS BLR (event_data)

    Reply
  • Mukul
    August 21, 2017 5:06 am

    i want to make a job which would run in every 4 hrs and output goes to .csv file on any location.
    output must contain the long running queries along with process id and along with some more detailing which is useful.

    Reply
  • stijn vervaet
    September 6, 2017 11:13 am

    Hello good folks! I’ve implemted the XE , at the same time keeping an eye on number of blocks by watching sys.dm_db_index_operational_stats. Now I see the No_Of_Blocks tick up, but nothing gets logged to the file.
    I have started the Event (as I see the file getting created). But is there anything I am missing here? Thanks 🙂

    Reply
  • John B
    March 28, 2018 10:57 pm

    You can view dead lock reports using SSMS.
    Just save the XML with a file name with an .XDL suffix.
    Then just open it using SSMS.

    Reply
  • Simply
    September 19, 2018 12:08 pm

    Hello,

    Is the event session stopped if the SQL Server Service restart?

    Can you please tell me how to automate the event session to start without accessing the SQL Server?
    —-
    ALTER EVENT SESSION [blocked_process] ON SERVER
    STATE = START;
    —–

    Thanks
    Simply

    Reply
    • willemwout
      September 20, 2018 4:40 am

      “Is the event session stopped if the SQL Server Service restart?” – What happened when you tried?
      “Can you please tell me how to automate the event session to start without accessing the SQL Server?” – google for Jobs. ( The SQLserver kind, not the Monsterboard ones.)

      Reply
  • The Kevin
    February 4, 2021 9:13 am

    Let me know if it’s me or something changed.

    I tried using this and got all the way to this point:
    EXEC sp_blocked_process_report_viewer @Trace=’bpr’, @Type=’TABLE’;

    It complained because it was expecting @Source as a parameter.

    Msg 201, Level 16, State 4, Procedure sp_blocked_process_report_viewer, Line 0 [Batch Start Line 0]
    Procedure or function ‘sp_blocked_process_report_viewer’ expects parameter ‘@Source’, which was not supplied.

    Completion time: 2021-02-04T12:12:32.3010195-05:00

    I changed it to this and it works.

    EXEC sp_blocked_process_report_viewer @Source=’bpr’, @Type=’TABLE’;

    Reply
  • Leszek
    April 30, 2021 1:09 pm

    Hi,
    One missing point, Should we not revert/disable what we enabled to get the logs after troubleshooting?
    Cheers

    Reply
  • Roopesh
    May 5, 2021 5:18 am

    Hi,

    I need extended events script for Azure SQL databases. Please help me on this.

    Reply
  • Ray
    August 25, 2021 9:09 pm

    I know this is an older thread, but this was epic! And to Michael J Swart, your work is amazing as well. Thank you both for saving SOOOOO much of my time.

    Reply
  • Albert
    December 1, 2021 3:48 am

    Hi,
    Any one else is getting this error?

    Msg 9415, Level 16, State 1, Line 1
    XML parsing: line 39, character 109, well formed check: no ‘<' in attribute value

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Brent Ozar

Hi! I’m Brent Ozar.

I make Microsoft SQL Server go faster. I love teaching, travel, cars, and laughing. I’m based out of Las Vegas. He/him. I teach SQL Server training classes, or if you haven’t got time for the pain, I’m available for consulting too.

Want to advertise here and reach my savvy readers?