Semi-Advanced Logging Options for SQL Server Agent Jobs

Many DBAs and organizations rely fairly heavily upon SQL Server Agent Jobs to cover a variety of tasks and needs – everything from routine maintenance and error-checking on up to regular processing of complex business logic or comprehensive import/export operations.

Yet, strangely, many organizations and DBAs commonly miss out on a number of easily-accessible advanced options that can make interacting with SQL Server Agent Jobs much easier. Especially when these jobs are bumping into problems and need to be more verbosely run/debugged or in cases where it might be helpful to have more comprehensive logging functionality.

Related: Tracking for Your SQL Server Agent Jobs

The Advanced Page for SQL Server Agent Job Steps

To take advantage of more verbose logging options, you just need to switch, or toggle, to the Advanced ‘page’ for the each individual Job Step for the SQL Server Agent Jobs you want to collect additional information on – as shown in the screenshot below:

Advanced Job Step Options / Features

Here’s a quick break-down of these optional logging features and options – and when/where they can make sense to use:

Output file
This option lets you direct the output from whatever is being executed in your job out to a log file. I personally like using this option in many cases when running DBCC CHECKDB and other corruption checks – as getting the output of all databases being checked in a single .txt file is much easier to parse through than scanning through SQL Server event logs. (That, and in many cases, it’s possible to tell how execution of DBCC CHECKDB went just by looking at the size of the .txt/.log file you specify for output.)

Log to table

I honestly don’t use this one very much – and I’m not sure why. But, it just outputs additional logging information into the sysjobsteplogs table (in the msdb database) – which stays fairly empty in most environments. But there’s no reason you couldn’t VERY easily leverage this table with some custom scripts to periodically scan it for various bits of text or certain outcomes after logging data to this table from various job steps where you might want to pick up additional information or context about execution outcomes.

Include step output in history

This is one of my favorite options to toggle in many cases when I’m troubleshooting a job/step that occasionally crashes and isn’t a mission critical job/step. By toggling this option on, it’s possible to get a better feeling (in many cases – depending upon the kind of operation being executed) on what’s going on or on what might be causing problems.

For example, instead of outputting results from DBCC CHECKDB into a .txt or other logging file, you can just as easily output the results of a given job-step into the History itself – making it easier to look at various outcomes if that makes more sense for how you prefer to review execution details/outcomes.

When / How to use Optional Logging Features

Many SQL Server Agent jobs run when you’re not sitting in front of your console – such as late at night or early in the morning. And while you may have a decent handle on what is probably going on with your server at that time of day, there may be environmental conditions you may not be anticipating. Consequently, if you bump into non-mission-critical jobs/steps that periodically fail and THINK it might be due to various other jobs/operations executing at roughly the same time and so on, it’s easy to use the advanced features listed above to put simple ‘debugging’ or instrumentation steps and details into play to see what’s going on with your server during execution times.

For example, you could query sys.dm_os_waiting_tasks or other DMVs/etc – and return the output DIRECTLY into your job steps. Similarly, if you think certain business rules or concerns might not be lining up correctly, you could put in simple SELECT statements to help troubleshoot – and the output of those queries can be dropped right into the execution outcome of your job steps – which can be VERY helpful when troubleshooting various failures.

So, in this regard, these additional features are great not only in making it easier to review the outcome of various maintenance jobs that you may already be executing but just wish to see better aggregation of outcomes, but can also become great tools for troubleshooting and debugging problems. 

Requirements / Caveats

In order to take advantage of these features there are a couple of caveats, requirements, and considerations to keep in mind.

First of all, and most obviously, when you enable the Log to Table option, you’re going to need to ensure necessary permissions, schema line-up, and you’ll have to account for the fact that logging to a table can and will take up additional space.

Similarly, when you use the Include step output in history option, you’ll end up using up more space within your msdb – because you’re obviously using up more logging space.

And when you use the Output file option, you’ll have to ensure that the service account that the SQL Server Agent is running under has MODIFY permissions on the directory where you’ll be outputting your more verbose logging options. 

Conclusion

These verbose logging options aren’t needed for every job/step – and would obviously be overkill for MANY job steps in most environments.

But they’re a great way to aggregate the results and outcome of many maintenance tasks – and can be powerful tools to help in debugging stubborn jobs or obtaining additional context when needed.

So, if you’d like to take these features for a test spin, I’ve created two simple SQL Server Agent Jobs / scripts that you can take a peek at to get a better feel for how these features work – without having to put forth much effort on your own. They’re both identical (and have to executed manually) in terms of how they kick off three bogus job steps – but one uses these more ‘verbose’ logging options while the other does not.