Azure SQL Database identify blocking source

Taiob Ali 101 Reputation points MVP
2021-07-07T20:15:55.44+00:00

Attention: @Dimitri Furman

For an Azure SQL Database, I have enabled 'Enable Azure SQL Auditing' with destination 'Log Analytics Workspace'. I also have diagnostic turned on at a database level with destination to the same 'Log Analytics Workspace'.

Now when I am parsing the blocked_process_filtered_s column using parse_xml function some of the values are marked as 'filtered'.
For example:
loginname="filtered"
hostname="filtered"

Why is that? How can I get the host and login name?

<?xml version="1.0" encoding="UTF-8"?>  
<blocked-process-report monitorLoop="523631">  
   <blocked-process>  
      <process id="process1e5f17ef468" taskpriority="0" logused="0" waitresource="OBJECT: 5:978102525:0 " waittime="26518" ownerId="22464123" transactionname="SELECT" lasttranstarted="2021-07-06T07:59:07.707" XDES="0x1e5dc37dbe8" lockMode="X" schedulerid="2" kpid="61140" status="suspended" spid="80" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2021-07-06T07:59:07.703" lastbatchcompleted="2021-07-06T07:59:07.607" lastattention="1900-01-01T00:00:00.607" clientapp="Microsoft SQL Server Management Studio - Query" hostname="filtered" hostpid="5036" loginname="filtered" isolationlevel="read committed (2)" xactid="22464123" currentdb="5" currentdbname="taiobtestads" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">  
         <executionStack>  
            <frame queryhash="0x5cbb5f2c0ae5b440" queryplanhash="0x16fbbac78e112e8f" line="1" stmtend="68" sqlhandle="0x020000005dc25d32ef099b94b452d2f6eab02f0daa2e14810000000000000000000000000000000000000000" />  
         </executionStack>  
         <inputbuf>filtered</inputbuf>  
      </process>  
   </blocked-process>  
   <blocking-process>  
      <process status="sleeping" spid="85" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-07-06T07:58:55.373" lastbatchcompleted="2021-07-06T07:58:55.373" lastattention="1900-01-01T00:00:00.373" clientapp="Microsoft SQL Server Management Studio - Query" hostname="filtered" hostpid="5036" loginname="filtered" isolationlevel="read committed (2)" xactid="22463875" currentdb="5" currentdbname="taiobtestads" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">  
         <executionStack />  
         <inputbuf>filtered</inputbuf>  
      </process>  
   </blocking-process>  
</blocked-process-report>  
Azure Monitor
Azure Monitor
An Azure service that is used to collect, analyze, and act on telemetry data from Azure and on-premises environments.
2,778 questions
Azure SQL Database
{count} votes

Accepted answer
  1. Dimitri Furman 156 Reputation points Microsoft Employee
    2021-07-09T18:54:28.83+00:00

    Hi @Taiob Ali - Saurabh is correct. The data being collected in this case is stored on Microsoft-owned systems before it gets to you Log Analytics workspace, and as such it needs to comply with strict data privacy and security requirements. The two fields you mentioned are considered PII data, therefore the data is intentionally removed.

    To troubleshoot blocking chains, you can start your own XE session and collect the blocked_process_report event, rather than the blocked_process_report_filtered event that is collected when you enable built-in auditing or monitoring features.

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful