Adding Log To Table to SQL Agent Job

Had to do this for a whole load of SQL servers with same job which was intermittently failing on random servers where we need more info / output and the “Log to Table” option* hadn’t been set.  Used a Powershell script to register all the Servers in Management Studio Server Group and then ran below query on all Servers in that Server Group

Also expanded msdb on all above servers to make a bit of space.

I like this method cos now we have the Server Group registered with all the servers we can then easily run another query against all the Servers in that Server Group to get the results / output.

(*only available in >=2005 so should really check .

See sp_update_jobstep for details on the flags for various advanced options

)


/*
SQLAgentAddLogToTable.sql

Adds “Log to table” / Append option on SQL Job step in named job

*/
SET NOCOUNT ON

DECLARE @JobName    sysname
DECLARE @Flags int
DECLARE @NewFlags int
DECLARE @ScheduleName sysname
DECLARE @job_name    sysname
DECLARE @job_stepname sysname
DECLARE @step_id int
DECLARE @SchedulesStartTime int
DECLARE @ScheduleId int

SET @JobName = ‘Some shonky job’

DECLARE STEPCURSOR CURSOR FOR
SELECT
    Jobs.name,
    Steps.step_name,
    Steps.step_id ,
    Steps.flags,
    Schedules.name,
    Schedules.schedule_id ,
    Schedules.active_start_time
FROM msdb.dbo.sysjobs as Jobs
LEFT OUTER JOIN
    msdb.dbo.sysjobschedules as JobSchedules
ON Jobs.job_id = JobSchedules.job_id
LEFT OUTER JOIN
    msdb.dbo.sysschedules  as Schedules
ON JobSchedules.schedule_id = Schedules.schedule_id
LEFT OUTER JOIN msdb.dbo.sysjobsteps Steps
    ON Jobs.job_id = Steps.job_id
WHERE Jobs.name = @JobName
AND Jobs.Enabled = 1
AND ( (next_run_date IS NOT NULL) AND (next_run_time IS NOT NULL))
AND ((next_run_date <> 0) AND (next_run_time <> 0))

OPEN STEPCURSOR
FETCH NEXT FROM STEPCURSOR INTO
    @job_name,
    @job_stepname,
    @step_id,
    @Flags,
    @ScheduleName,
    @ScheduleId,
    @SchedulesStartTime

   
PRINT @@Servername
   
WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT ‘*** Processing Step ‘ + @job_stepname
        — ie if not log to table / append
        IF (@Flags & 16) <> 16
            BEGIN
                SET @NewFlags = @Flags + 16
                PRINT ‘Updating stepname  = ‘ + @job_stepname + ‘ to log to table (NewFlags = ‘ + CAST(@NewFlags as varchar(25)) + ‘)’
                EXEC msdb.dbo.sp_update_jobstep @job_name = @job_name, @step_id = @step_id, @flags = @NewFlags
            END
        ELSE
            BEGIN
                PRINT ‘stepname = ‘ + @job_stepname + ‘ already has log to table / append set’
            END

    FETCH NEXT FROM STEPCURSOR INTO
    @job_name,
    @job_stepname,
    @step_id,
    @Flags,
    @ScheduleName,
    @ScheduleId,
    @SchedulesStartTime
END
CLOSE STEPCURSOR
DEALLOCATE STEPCURSOR

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s