Checking for database deadlocks on Azure SQL

Recently, we've been trialing Azure's SQL Data Sync (in Preview at time of writing) feature which allows you to sync database schema and data between multiple databases. This has been working great but we've run into some issues with database triggers so we wanted to dig down deeper.

Checking for irregularities

use master

-- check for recent events
select top 1000 * from sys.event_log order by start_time desc

-- filter by severity
select top 100 * from sys.event_log where severity > 0 order by start_time desc

To view more details about the dead locks

use master

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

References

https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/01/21/lesson-learned-19-how-to-obtain-the-deadlocks-of-your-azure-sql-database/

comments powered by Disqus