![]() It can be good idea to compress the folder into a zip file. ![]() Probably you will share this on a shared folder or even by email. The output on the folder will be something like: Now that we have the T-SQL to get the data, we just need to save it on some folder.Įach outputted file name have a name like deadlock_ WHERE object_name like 'xml_deadlock_report' Using PowerShell to save the files to the filesystem SELECT CONVERT(xml, event_data).query( '/event/data/value/child::*') as deadlock,ĬONVERT(xml, event_data).value( 'datetime') AS Execution_TimeįROM sys.fn_xe_file_target_read_file( + '\system_health*.xel', null, null, null) SET = SUBSTRING( 1, charindex( '\ERRORLOG', - 1) You can use, for example, the following query to get the ErrorLog file path:ĭECLARE NVARCHAR( 255) = ( SELECT CAST(SERVERPROPERTY( 'ErrorLogFileName') AS NVARCHAR( 255))) The only thing you need to know is the path where the system_health extended event is saving the results. This query will show you when the deadlock happened (datetime) and the XML of the deadlock. This works on SQL Server 2012 or higher version.įor a better overview I recommend you to read the What are SQL Server deadlocks and how to monitor them article from SQLShack. In this post I will share how you can do it from all files that belongs to the system_health extended event session. How do we get the deadlocks?ĭepending on the version of SQL Server that you are running, there are different ways to do it. ScenarioĬlient wants to analyze most recent deadlocks that happened on a specific instance. Just a quick post as may help any of you searching for this.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |