Turn on Job Logging to Table
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Date: 11 Sep 2009
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.