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