Monday, November 2, 2015

SQL Agent job is failing with Trace file name '' is invalid error



SQL Server Agent Job is failing with Trace file name '' is invalid
Message
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
(OR)
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
(or)
select * from sys.fn_trace_getinfo(NULL)
(or)
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)
reconfigure