In addition of my precedent blog about this subject “SQL Server Tips: Default trace enabled but no file is active…”, I add a new case where the default path of the trace file was empty.
The first step was to verify if the default trace is enabled with the command:
SELECT * FROM sys.configurations WHERE name=’default trace enable’
It is enabled, then I check the current running trace with the view sys.traces
SELECT * FROM sys.traces
As you can see, this time I have a trace but with a null in the Path for the trace file…
To correct this issue, the only way is to stop and reactive the trace in the configuration:
EXEC sp_configure 'show advanced options',1; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'default trace enabled',0; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'default trace enabled',1; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'show advanced options',0; GO RECONFIGURE WITH OVERRIDE; GO