These are my SQL default trace files:

enter image description here

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   
                           FROM    sys.fn_trace_getinfo(NULL) f
                           WHERE   f.property = 2)), DEFAULT
                        ) tf

The output is:

enter image description here

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?


1 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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.