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