Today there was an interesting question on max_files column value which is available under sys.traces view object.The question was like this  -

max_files in sys.traces is 0 and that means zero, not NULL – correct?

Before we get into the details,I would like to mention that the max_files column means the maximum number of rollover files.When we create a trace we have the option to enable file roll over,which simply means that if the first trace file size is set as 5MB,then after reaching that limit sql profiler will create another file to continue capturing the trace.This will continue until and unless the trace is stopped.

Now,can ever this max_file value be NULL ? Yes,after research I found that this value will be NULL if the is_rollover flag is set as 0(Ie,we dont choose enable file rollover when creating the trace)

select max_files,is_rollover,id AS Trace_ID from sys.traces
WHERE id <>1 --Filer DefaultTrace

Another interesting fact is related to default trace.The default trace max_file value is 5 and max_size is 20 and this cannot be changed.

Thanks for reading.