SQL Server Agent Job is failing with Trace file name '' is invalid
Executed as user: NT SERVICE\SQLSERVERAGENT. Trace file name '' is invalid. [SQLSTATE 42000] (Error 19050) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
Steps to resolve issue:
1. We need to make sure whether default tracing is enabled or not, we can check this using below options:
sp_configure 'default trace enabled' -- If the run_value is 1 then the default trace for SQL Server is enabled
SELECT* FROM sys.configurations WHERE configuration_id = 1568
2. Once you make sure the trace is enabled then you need to verify what is the default trace file, below are the differnt ways to identify it
select * from sys.traces
select * from sys.fn_trace_getinfo(NULL)
select * from :: fn_trace_getinfo(default)
In my case though the default trace is enabled there is no trace file generated by SQL Server for some reason, so I have enabled and disabled the default trace option fixed the issue.
Enable / Disable default trace in SQL Server
sp_configure 'default trace enabled', 1 (1 is enable 0 is for disable)