![]() This works ‘datetime’) as UTC_event_time, If the extended event is created in the default location sys.fn_xe_file_target_read_file works without the full path of the file, just the file name is required. To further analyze the deadlock, just click on the deadlock report link. WHERE OBJECT_NAME like 'xml_deadlock_report' SET = 38, CHARINDEX('MSSQL\Log', - 29) ĬONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,įROM + '\system_health*.xel', NULL, NULL, NULL) ![]() INSERT INTO #errorlog EXEC sp_readerrorlog To avoid this issue, you can run the below script Extracting Deadlock information without keying the path CREATE TABLE #errorlog ( WHERE object_name like 'xml_deadlock_report'īut the problem with above query, you need to keep changing the location, if you are going to run it on the different – different servers as the path (C:\Program Files\….) may not be the same. ('C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2K16DEV01\MSSQL\Log\system_health*.xel', null, null, null) SELECTĬONVERT(xml, event_data).query('/event/data/value/child::*'), XEL files using the sys.fn_xe_file_target_read_file function to retrieve the deadlock information. We can directly query to SYSTEM_HEALTH trace. ![]() The system_health extended events session can be a gold mine for researching deadlocks and many issues. It keeps historical system health information of the SQL Server instance. Today, we are going to explore how quickly we can read the Deadlock information using Extended Event – “Without passing the default location of the extended events trace files” Extended Events is a powerful feature that was introduced into SQL Server 2008 and onwards. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |