Job Step Logging

The SQL Server Agent reporting engine prints output to the console. Therefore it needs to be persisted. First let's cover available options for output persistence.

SQL Server Agent enables the user to optionally log job step output beyond what gets recorded into msdb.dbo.sysjobhistory table. If users are members of the sysadmin fixed server role, they can choose to log to a file. File logging is performed under the credentials of the SQL Server Agent service account. The most common way of logging job step output and the only way for non-administrator users (starting in SQL Server 2005) is to log to the msdb.dbo.sysjobstepslogs table. Logging to a table has options similar to those of logging to a file, such as append and overwrite. If a user chooses to append the output, a new row is created for each new run. Overwrite actually replaces the previous entry to the table. It was not an easy decision for the development team to completely change logging models in this release, potentially breaking the setups of existing customers. However, logging to a file could not be done securely without introducing a special proxy account. If all users used the same proxy account for logging, they could potentially read each other's logs, thus leading to information disclosure. Alternatively, SQL Server Agent could continue to log with a highly privileged service account but restrict the output to a special folder. That could potentially have the same affect. The final choice was made in favor of a correct long-term strategy. Indeed, because all SQL Server Agent metadata is stored in MSDB tables it seems natural to store logs to a table as well.

Getting back to the scenario, all you need to do is check the option of logging to table as well as the sub-option of appending the output, as shown in Figure 8-12.

Figure 8-12. Job step advanced options.