Tag: Azure

How to see deadlocks in SQL Azure

Capturing deadlock is little bit different in AZure Databases.We have two ways to capture deadlock…

----Option 1:Run on master database

SELECT * FROM sys.event_log
WHERE database_name like 'test'
AND event_type = 'deadlock'

recently we experienced sys.event_log(ony this dmv) is taking so much time for simple query as well.With the help of CSS we got below query which is way faster and captures relevant details..

--option 2:
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('el', null, null, null)
              where object_name like '%deadlock%'

Thats it,have Funn..