How do I identify a deadlock in SQL Azure?

Question

I have a Windows Azure role that consists of two instances. Once in a while a transaction will fail with an SqlException with the following text

Transaction (Process ID N) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Now I've Googled for a while and read this post about identifying deadlocks using SQL Server logs.

The problem is...

How do I do it in SQL Azure? What tools do I use to access the internals of SQL Azure and getting enough data?

Rosana

If you cannot find and acceptable answer here don't hesitate to contact Microsoft directly. Microsoft is pouring a lot of resources into Azure and the support that they are giving Azure developers since Mr. Guthrie took over is rather impressive. If that fails I'll keep an eye out on this thread and direct Azure MVPs who I know to answer this thread as I'd like to know the answer myself. – Broomrape

Answer 1

Monitoring of SQL Azure is more limited than SQL Server, but the tools are becoming more available for you to look underneath:

http://social.technet.microsoft.com/wiki/contents/articles/troubleshoot-and-optimize-queries-with-sql-azure.aspx

Charters

Answer 2

Run the following query on "Master" database in SQL Azure db,

select * from sys.event_log where event_type='deadlock' and database_name='<Databasename>';

There was a performance issue with this query, if it gets timed out try following,

SELECT *
,CAST(event_data AS XML).value('(/event/@timestamp)[1]', 'datetime2') AS TIMESTAMP
, CAST(event_data AS XML).value('(/event/data[@name="error"]/value)[1]', 'INT') AS error
,CAST(event_data AS XML).value('(/event/data[@name="state"]/value)[1]', 'INT') AS STATE
,CAST(event_data AS XML).value('(/event/data[@name="is_success"]/value)[1]', 'bit') AS is_success
,CAST(event_data AS XML).value('(/event/data[@name="database_name"]/value)[1]', 'sysname') AS database_name
FROM sys.fn_xe_telemetry_blob_target_read_file('dl', NULL, NULL, NULL)
WHERE object_name = 'database_xml_deadlock_report'

Second query has data in XML format relating to the processes being executed. Good luck!

Neoimpressionism

I dont think either of these queries work in v12. For me the first query from sys.event_log returns rows, but no XML. On the same database the second query returns no rows. – Harken

Have tried both the above queries and neither of them are returning the deadlock xml. First query will not complete, second query runs but the event additional_data column is NULL Azure SQL Database version is Microsoft SQL Azure (RTM) - 12.0.2000.8 Oct 1 2020 18:48:35 Copyright (C) 2019 Microsoft Corporation – Antifriction

Answer 3

Now Azure SQL database supports two ways to get deadlock xml reports. You can create a db-scoped XE session with the database_xml_deadlock_report event to track them yourself, or you can modify the sys.fn_xe_telemetry_blob_target_read_file call from the earlier answer to use 'dl' instead of 'el'. Deadlocks are now routed to their own file instead of being mixed in with login events.

This MSDN article has the latest information.

Hatten