Why does fn_trace_gettable with the DEFAULT parameter not roll over all trace log files?

Question

These are my SQL default trace files:

To review the trace logs I run the following query:

SELECT min(tf.StartTime) MIN_STARTTIME, max(tf.StartTime) MAX_STARTTIME
FROM ::fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1   
                           f.[value]
                           FROM    sys.fn_trace_getinfo(NULL) f
                           WHERE   f.property = 2)), DEFAULT
                        ) tf

The output is:

MSDN doc:

sys.fn_trace_getinfo ( { trace_id | NULL | 0 | DEFAULT } )

Specify NULL, 0, or DEFAULT to return information for all traces in the instance of SQL Server.

Based on the above my query is selecting top 1 which is the latest file.

fn_trace_gettable ( 'filename' , number_files )

If number_files is specified as default, fn_trace_gettable reads all rollover files until it reaches the end of the trace.

Since the fn_trace_gettable has parameter DEFAULT, should the start time not have been an earlier time (for example: 20-09-2022)? Why doesn't it roll over to the other files?

Answer 1

sys.fn_trace_getinfo(NULL) returns the last (latest) file name.

fn_trace_gettable ( 'filename' , DEFAULT ) will roll over only if the file is not the last (latest) file. I tried this by hardcoding the file path of one of the previous files, it reads the file and rolls over into the recent files.

Not sure how to get the 1st (earliest) file name programmatically. So for now I'm hardcoding the 1st trace log file name as I can see it easily by goin into the LOGS directory.