[System_Health] pulling all the data from the saved-to-disk files

TL;DR - imports the 3-4 system_health files that are automatically saved to disk.

I had a problem this week with a deadlock.  I went looking in the ring buffer for the system_health session, but it had already aged out. 

So, remembering that the system_health session has a file target, went looking for it (Jason Brimhall has code for it - thanks!).  The file went back further but not all the way.  Since the event definition says to keep a couple of older files, I use the current file to find the path, list all the files in that path (xp_dirtree; doesn't require xp_cmdshell), then walk through and import all of them. 

You could simplify it a lot and use fn_xe_file_target_read_file and tell it to read all files like system_health*0.xel in the path, but with my luck those ending numbers will sometimes end in a non-0 value.  I use the 0 in the mask because I make local copies of the files occasionally, so that weeds out " - Copy".

When I get some free time, I'll get my current sp_server_diagnostics/system_health parsers to work with it (see http://thebakingdba.blogspot.com/2015/04/systemhealth-2-dynamic-and-full-parsers.html and http://thebakingdba.blogspot.com/2012/12/spserverdiageventparser-make.html).  I started on that, but it's cartesianing on me and I don't have time to fix it right now. 

Enjoy!

	--TL;DR thebakingdba.blogspot.com
	--Purpose: find and read your system_health files.
	--uses Jason Brimhall's code to find the event_file target via DMV, then uses that as a base folder for the rest.
	--http://jasonbrimhall.info/2015/06/21/reading-extended-event-file-session-data/
	declare @full_systemhealth_file varchar(1000), @systemhealth_path VARCHAR(512)
	DECLARE @full_directory_listing TABLE (subdirectory VARCHAR(1000),depth SMALLINT, [FILE] smallint)
	DECLARE @system_health_files TABLE (id INT IDENTITY, XE_filename VARCHAR(1000))
	if object_id('tempdb..#xe_file_target_results') is not null
	DROP TABLE #xe_file_target_results
	CREATE TABLE #xe_file_target_results
	(XE_object_name NVARCHAR(256), event_data XML, xe_filename VARCHAR(500), file_offset BIGINT)
	--get the name of the current system_health file
	SELECT @full_systemhealth_file = FileTarget.value('@name','varchar(1000)') from
	--do this so you get the XML to parse with Xquery
	(SELECT target_data = CONVERT(XML, target_data)
	FROM sys.dm_xe_session_targets t
	INNER JOIN sys.dm_xe_sessions s
	ON t.event_session_address = s.address
	WHERE t.target_name = 'event_file') cte1
	--then this gives you the file target name
	CROSS APPLY cte1.target_data.nodes('//EventFileTarget/File') FileEvent ( FileTarget )
	--Use xp_dirtree to get ALL the system_health files in that folder, since that's where the old ones should reside
	--I could have just called "sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\system_health*0.xel', NULL, NULL, NULL)"
	-- but my concern was that the tag at the end might not always end in 0. On the plus side, xp_dirtree doesn't require xp_cmdshell!
	SELECT @systemhealth_path = LEFT(@full_systemhealth_file, len(@full_systemhealth_file) - charindex('\',reverse(@full_systemhealth_file))) + '\'
	--once we have the path, list all files in that path.
	INSERT INTO @full_directory_listing
	EXEC xp_dirtree @systemhealth_path, 1, 1
	--make a list of system_health files that aren't copies
	INSERT INTO @system_health_files (XE_filename)
	SELECT subdirectory FROM @full_directory_listing
	WHERE [FILE] = 1
	AND subdirectory LIKE 'system_health%.xel'
	AND subdirectory NOT LIKE '%copy%'
	AND subdirectory NOT LIKE '% %'
	--Now import each file
	declare @min INT, @max INT, @filename VARCHAR(500)
	SELECT @min = MIN(id), @max = MAX(id) FROM @system_health_files
	while @min <= @max
	BEGIN
	print @min
	SET @filename = NULL
	SELECT @filename = xe_filename FROM @system_health_files WHERE id = @min
	INSERT INTO #xe_file_target_results
	SELECT [object_name] AS XE_object_name, event_data, @filename AS xe_filename, file_offset
	FROM sys.fn_xe_file_target_read_file(@systemhealth_path + '\' + @filename, NULL, NULL, NULL)
	set @min = @min+1
	END

view raw system_health_import_all_event_file hosted with ❤ by GitHub