If you want to investigate a case the default trace setting can be readable for few day and after that they are overwritten.
The default setting is 10 x 100 MB files.
You can check the oldest log:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
DECLARE @file_name AS nvarchar(max); DECLARE @file_path AS nvarchar(max); SELECT @file_name = CAST(st.target_data AS xml).value( N'(EventFileTarget/File/@name)[1]', N'nvarchar(max)') FROM sys.dm_xe_sessions s INNER JOIN sys.dm_xe_session_targets st ON s.[address] = st.event_session_address WHERE st.target_name = 'event_file' AND s.[name] = 'system_health'; SELECT @file_path = LEFT( @file_name, LEN(@file_name) - CHARINDEX('\', REVERSE(@file_name)) + 1); SELECT files.[file_name], MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)[1]', N'datetime')) AS oldest_event FROM sys.fn_xe_file_target_read_file ( @file_path + 'system_health*', null, null, null ) files GROUP BY files.[file_name] OPTION(NO_PERFORMANCE_SPOOL, QUERYTRACEON 8649); |
Hey , it is only 3 days! you have to backup the files. A better solution to survive a long weekend to increase the retention, for example using 30 files:
|
1 2 3 4 5 6 7 8 |
ALTER EVENT SESSION [system_health] ON SERVER DROP TARGET package0.event_file ALTER EVENT SESSION [system_health] ON SERVER ADD TARGET package0.event_file ( SET filename=N'system_health.xel', max_file_size=(100), max_rollover_files=(30) ) |