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

ċ
usp_TurnOnScheduledJobLoggingToTable.sql
(1k)
Andy Hughes,
Jul 24, 2012, 7:11 AM
Comments