SQL Sentry Job Step / SSIS Logging
Introduction
The SQL Sentry Event Calendar can collect and display extended step log data for SQL Server agent job steps on the SQL Server 2005 and above. For this data to be collected, certain options must be enabled at the job step level.
These options are only available for the following step subsystems:
- Operating System (CmdExec)
- Analysis Services Command
- Analysis Services Query
- Transact-SQL script (T-SQL)
By default, the step history for SQL Server agent jobs includes a maximum of 1024 characters of output, and it's not typically cleanly formatted. If you need to see more of the history data using the native output format, in the SQL Server 2005 and above you can enable extended logging for a job step. Once enabled, SQL Sentry collects and process the first 512 KB of step output.
To enable extended logging for a job step, open the properties for the job step in SQL Server Management Studio, and then select Edit.
Select Advanced on the left side of the form, choose Log to table to enable extended step logging, and then select OK.
Once enabled, extended job step log data can be viewed in the SQL Sentry client, both in the job Step Callout windows on the Calendar pane and in the History List view. A new level is added underneath any job steps with extended logging enabled. Select [+] to expand the level and view the history details.
For job steps where SSIS Logging has been enabled through the SQL Sentry client, the step log is parsed and displayed as one row per SSIS event.
On watched SQL Server 2005 and above instances, SQL Server Agent Jobs that have one or more steps with a subsystem type of SQL Server Integration Services package has an SSIS Logging Options command within their context menu; this includes SQL Server Maintenance Plan jobs. The SSIS Logging Options command makes it easy to enable or disable SSIS Logging.
Once SSIS Logging has been enabled for a SQL agent job, a new message text level is added beneath any SSIS job steps in both the Event Calendar step callouts and in History List views. A row is added for each SSIS event, up to the first 512 KB of log data.
When you enable SSIS Logging for a SQL agent job, the following takes place:
- All SSIS subsystem steps change to CmdExec subsystem steps.
- The append /CONSOLELOG and other appropriate parameters are added to the command line of each SSIS step.
- The Log to Table option is set for each SSIS step.
Logging Level
The SSIS Logging Options dialog box allows you to set the Logging Level to one of the following:
| Setting | Description |
|---|---|
| Disabled | SSIS log data isn't collected or displayed. |
| Enabled | SSIS log data is collected and displayed for the following package events:
|
| Enabled (Verbose) | SSIS log data is collected and displayed for all package events. Since large amounts of data are collected and processed with verbose logging, to avoid possible performance issues, it is highly recommended that this setting only be used for troubleshooting purposes and is immediately set back to Enabled when complete. |
Run As
When enabling SSIS Logging, using the Run As drop-down list, select to run the package as follows:
| Run As | Description |
|---|---|
| Keep current proxy settings | When you select Keep current proxy settings, the account specified within the Job Step properties is used to run the SSIS job step. Note: The current proxy must have active rights to the operating system (CmdExec) subsystem to enable SSIS logging within SQL Sentry. |
| SQL Agent Service Account | When you select SQL Agent Service Account, the SQL Server agent service account is used to run each SSIS job step instead of the account specified within the Job Step properties. |
| Additional proxy accounts | Additional proxy accounts with the necessary operating system (CmdExec) subsystem rights are also listed as an option. |
Changing the SSIS Logging options for a job triggers the event object: properties modified audit conditions within SQL Sentry.