Increased the default XEvent trace: system_health size and retention policy are in SQL Server

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:

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);

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:

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)
)

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)

)

Leave a Reply Cancel reply