• You need to build some logic into the Agent jobstep to check if the trace is allready running. If the trace definition doesn't exist you need to create and start the trace. If the trace is stopped you'll need to start the trace. If the trace is running just end the jobstep.

    -- get trace information

    SELECT

    [traceid]

    ,[property]

    ,[tpdesc] =

    case

    when [property] = 1 and [value] = 1 then N' produces a rowset.'

    when [property] = 1 and [value] = 2 then N' creates a new file when max file size is reached.'

    when [property] = 1 and [value] = 3 then N' create a new file when max file size is reached and produce a rowset.'

    when [property] = 1 and [value] = 4 then N' shuts down the trace on an error.'

    when [property] = 1 and [value] = 5 then N' produces a rowset and shutdown on error.'

    when [property] = 1 and [value] = 6 then N' creates a new file when max file size is reached and shuts down on an error.'

    when [property] = 1 and [value] = 7 then N' creates a new file when max file size is reached, produces a rowset and shuts down on an error.'

    when [property] = 1 and [value] = 8 then N' is a Blackbox trace.'

    when [property] = 2 then N' results are in file ' + cast([value] as nvarchar(245)) + N'.trc.'

    when [property] = 3 then N' max file size is ' + cast([value] as nvarchar(5)) + N' megabytes.'

    when [property] = 4 and [value] is not null then N' automatically stops on ' + cast([value] as nvarchar(25)) + '.'

    when [property] = 4 and [value] is null then N' does not automatically stop on any date and time.'

    when [property] = 5 and [value] = 0 then N' is stopped.'

    when [property] = 5 and [value] = 1 then N' is running.'

    end

    FROM :: fn_trace_getinfo(0)

    ORDER BY [traceid],[property] DESC

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **