Turn on Job Logging to Table

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 11 Sep 2009

Description

I developed this piece of code several years back to address an annoying hole in the SQLAgent job reporting and Maintenance Plan offering.

SQL Server Maintenance Plans have their uses, but I find mainly these mainly to be on smaller scale installations. If I need a more Enterprise level solution, then I'll go with either a 3rd party solution or a more hand-coded approach like the one from Ola Hallengren.

With the out of the box solution, there is one problem when examining the results from job executions. It's not restricted to just Maintenance Plan jobs, but these have the most verbose output and therefore the problem is more noticeable - the job output results are limited to the first several hundred characters of text. This becomes a problem when jobs like DBCC CheckDB run, as these can have a large output and any errors can be out of bounds of the standard text display.

There is an option for each step of a SQLAgent job (in the advanced section) to turn on step logging to table. This copies the full text of the job output to the msdb.sysjobstepslog table. The GUI even provides a method for easily viewing the log on a per-step basis, but I prefer to write reports that contain all steps for all jobs and display them through SSRS.

This is a great bit of foresight by Microsoft....BUT.... any time a scheduled Maintenance Plan is edited and saved, the SQLAgent job is dropped and recreated from scratch, and setting this flag is not an option provided in the Maint Plan GUI.

I wrote this code as I was fed up with forgetting to manually reset the flag after editing a Maint Plan.

I then scheduled the SP to run every evening before the maintenance window so that the flag would be reset for all jobs.

Code

Stored Procedure:

DROP PROC usp_TurnOnScheduledJobLoggingToTable

GO

CREATE PROC usp_TurnOnScheduledJobLoggingToTable

AS

/****************************************************

Purpose: To switch on logging to table for all

SQL Agent Scheduled jobs

Aimed specifically at MaintPlan jobs as

the flag gets reset every time the parent MaintPlan is

altered (for all steps in the plan)

Author: ChillyDBA

History: 11 Sep 09 - Intial Issue

*****************************************************/

DECLARE @JobName SYSNAME,

@Step_ID INT,

@Cmd VARCHAR(1000)

SELECT @JobName = MIN(Name)

FROM msdb..sysjobs (NOLOCK)

WHILE @JobName IS NOT NULL

BEGIN

SELECT @Cmd = ''

SELECT @Step_ID = MIN(Step_ID)

FROM msdb..sysjobsteps sjs (NOLOCK)

INNER JOIN msdb..sysjobs sj (NOLOCK) ON sjs.Job_ID = sj.Job_ID

WHERE sj.Name = @JobName

WHILE @Step_ID IS NOT NULL

BEGIN

SELECT @Cmd = @Cmd +

' use msdb exec sp_update_jobstep @job_Name=N''' +

@JobName +

''', @step_id= ' +

CONVERT(VARCHAR(10), @Step_ID) +

' ,@flags=8'

EXECUTE(@Cmd)

--SELECT @cmd, @JobName, @Step_ID

SELECT @Step_ID = MIN(Step_ID)

FROM msdb..sysjobsteps sjs (NOLOCK)

INNER JOIN msdb..sysjobs sj (NOLOCK) ON sjs.Job_ID = sj.Job_ID

WHERE sj.Name = @JobName

AND Step_ID > @Step_ID

END

SELECT @JobName = MIN(Name)

FROM msdb..sysjobs (NOLOCK)

WHERE Name > @JobName

END