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
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)