Generating package0.event_file to see deadloack info

Question

Hi Team,

I am working on resolving a deadlock issue with a table in Azure SQL Database.

In order to find the root cause of deadlock, after connecting with the SQL Server of the database, I am trying to find the package0.event_file which is in the Management -> Extended Events -> Sessions -> system_health (inside SSMS), but I am not seeing any file under Sessions -

1. Can you please let me know how to generate package0.event_file in above case.
2. Once above file is generated, do I need to perform some other activity to get the deadlock data populated in package0.event_file?
3. Are there any other tools which will help me get to know the reason of deadlock (happening in a table of Azure SQL Database)?

If possible please provide me links along with solution.

Please let me know if I need to clarify more on any of above points.

Thanks,

Salil

Answer 1

Hi salilsingh-9961 •,

Thanks for posting this question in Micrsoft Q&A forum.

As I understand, you want to see deadlock information in Azure SQL Database.

Deadlock graphs are a rich source of information regarding the processes and locks involved in a deadlock. To collect deadlock graphs with Extended Events (XEvents) in Azure SQL Database, capture the sqlserver.database_xml_deadlock_report event.

https://learn.microsoft.com/en-us/azure/azure-sql/database/analyze-prevent-deadlocks?view=azuresql&tabs=ring-buffer#collect-deadlock-graphs-in-azure-sql-database-with-extended-events

If you have set up an XEvents session to collect deadlocks and a deadlock has occurred after the session was started, you can view an interactive graphic display of the deadlock graph as well as the XML for the deadlock graph.

https://learn.microsoft.com/en-us/azure/azure-sql/database/analyze-prevent-deadlocks?view=azuresql&tabs=ring-buffer#view-deadlock-graphs-from-an-xevents-session

Viewing a deadlock graph in XML format allows you to copy the inputbuffer of Transact-SQL statements involved in the deadlock. You may also prefer to analyze deadlocks in a text-based format.

If you have used a Transact-SQL query to return deadlock graph information, to view the deadlock graph XML, select the value in the deadlock_xml column from any row to open the deadlock graph's XML in a new window in SSMS.

https://learn.microsoft.com/en-us/azure/azure-sql/database/analyze-prevent-deadlocks?view=azuresql&tabs=ring-buffer#view-and-save-a-deadlock-graph-in-xml

To save the deadlock graph as an XML file:

1. Select File and Save As....
2. Leave the Save as type value as the default XML Files (*.xml)
3. Set the File name to the name of your choice.
4. Select Save.

https://learn.microsoft.com/en-us/azure/azure-sql/database/analyze-prevent-deadlocks?view=azuresql&tabs=ring-buffer#save-a-deadlock-graph-as-an-xdl-file-that-can-be-displayed-interactively-in-ssms

Other Links:

https://learn.microsoft.com/en-us/azure/azure-sql/database/analyze-prevent-deadlocks?view=azuresql&tabs=ring-buffer

https://www.sqlshack.com/monitoring-sql-server-deadlocks-using-the-system_health-extended-event/

Please let us know if this helps or if you have further queries.

Awaiting your reply. Thanks

Answer 2

To get information about deadlocks please connect to the Master database on Azure SQL logical server and run the following query:

WITH CTE AS (
SELECT 
    CAST(event_data AS XML)  AS [target_data_XML]
FROM 
    sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null)
)
SELECT 
    target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2') AS Timestamp,
    target_data_XML.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
    target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(100)') AS db_name
FROM CTE

The deadlock_xml column will give you the deadlock information in XML format, but if you want to see the deadlock graph instead, copy the deadlock_xml column results from the previous query and load into a text file. If more than one row is returned, you will want to do each row result separate. Save the file as a '.xdl' extension, (e.g. deadlock.xdl) which can be viewed in tools such as SQL Server Management Studio (SSMS) as a deadlock report/graphic. Open the .xdl file with SSMS.