In this month’s column, we show you a technique to configure logging for multiple SQL Server Agent Job Steps, using PowerShell. We also review logging options and Agent tokens, a topic that is often overlooked.
SQL Server Agent offers various options for the logging of a job execution. The following section reviews the basics.
Example: You can build a job named “Test Logging” with a single step that updates statistics for the AdventureWorks2008R2 database by executing the sp_updatestats stored procedure.
When you run the job, output results are written by default into the system table sysjobhistory in the msdb database. SQL Server Management Studio provides a GUI to review this output, eliminating the need to directly query the system table.
One problem with the default approach, however, is that the output is limited to 1024 (Unicode) characters, because the “message” field of the sysjobhistory table is of type nvarchar (1024) and it may not be enough, in some cases. For example, with sp_updatestats, the output will typically become truncated. You can find the script of the job in Script-J2-Test-Logging-Job-log-to-table . This may not be a problem, but if you want to do further analysis on the output, you clearly don’t want to miss any information.
A better approach is to enable the “log to table” option in the advanced pane of each Job Steps’ properties configuration, as the following illustration suggests:
Script-J5-Test-Logging-Job-final creates a job that logs to table. With this option set, you will still have the truncated output in the job’s history system tables, but you can return to the configuration pane and click the view button; SSMS will extract the output to a temporary file and open it with your default editor.
This is not particularly helpful if you want to perform analyses; imagine having a multi-step job updating statistics for many databases and having to go to each step’s advanced options to see the results.
You can get to the same information through another system table called sysjobstepslogs, as in the following example: (The script can be found inScript-T1-Query-sysjobstepslogs (2)
However, many cases, you will still need to extract the data to do further analyses. For example, assume you want to use regular expressions to find which statistics have been updated, and you are not allowed to build a CLR user-defined function/procedure to do this; CLR is not enabled in that instance. Moreover, you will have to provide extra steps to manually keep this log table clean.
A different approach that could better fit your needs is to specify a file for the step’s output, as shown in the following illustration:
C:TempAgentLogsTest Logging - (ESCAPE_NONE(STRTDT))$(ESCAPE_NONE(STRTTM)) - Update Stats AdventureWorks2008R2.txt
Use Script-J3-Test-Logging-Job-log-to-file to create this job.
This may be more practical, but there are still a couple limitations. If you don’t specify to append the output to the existing file, you will need to process the file before the job runs again; otherwise, you risk losing some information. On the contrary, if you specify to append the output, you will never miss the information, but your scripts to do further analyses may need to keep track of what you’ve already analyzed. Furthermore, handling retention of this information is complex.
Things would be simpler if you could output to different files with their names dynamically generated.
SQL Server Agent Tokens
The easiest way to dynamically generate a name is to use SQL Server Agent tokens. Agent tokens are still an obscure feature for many, despite being available since the advent of SQL Server 2000. A long time ago, at a TechEd session about SQL Server Agent internals, the presenter, Gert Drapers, talked about this feature, which did not have documentation at the time, a rather shocking revelation.
The syntax of these tokens has changed a couple of times since then, partly to prevent injection issues. This may be another reason why they are not too popular (imagine installing Service Pack 1 for SQL Server 2005 and suddenly having to change all the jobs to use tokens).
Agent tokens are just placeholders for values that will be provided at run-time by the SQL Server Agent engine right before execution. They are usually used in Job Steps’ command definitions to provide values for current date and time, instance name, or WMI information returned by events that trigger alerts.
If you are not familiar with Agent tokens, you can find more information about them in Books Online under the “Using Tokens in Job Steps” topic or online at the following address: http://msdn.microsoft.com/en-us/library/ms175575.aspx
How can you make use of the Agent tokens to generate dynamic output file names? Tokens are used not only in the job step command, but also in the definition of the output file name. For example, you can configure the filename as follows:
The sample script can be found in Script-J4-Test-Logging-Job-log-to-file-with-tokens.
The token STRTTDT will be replaced, at run-time, with the date in YYYYMMDD format, when the job executes, while the token STRTTM will be replaced with time in HHMMSS format. For example, the output generated by the execution of the step will be written to the file Test Logging – 20110412104857 – Update Stats AdventureWorks2008R2.txt in the directory C:TempAgentLogs.
Automatically configuring output file names
You may wonder why this discussion about logging and tokens is taking place in a PowerShell article. First of all, it is a good opportunity to review a few key functionalities of SQL Server Agent. More importantly, consider the following scenarios:
-
you are currently not using logging to file and/or tokens for filenames, and you have several jobs for which you want to start using these functionalities;
-
you have several jobs already configured to use logging to file and/or tokens, and you need to change the filename for a subset of them; perhaps, because you need to change the path where logs are written;
Please note that, even for a single job, you may have several different job steps for which the output file name needs to be changed. Without using scripting to do this, you must alter every single job step in every single job to be configured. But with PowerShell, it’s very easy to change the configuration of log files for several job steps. If you define and follow a convention for jobs and job steps naming, you can create a good standardization of log file names.
For example: there is a token to provide the job Id value, although this is not particularly useful for inclusion in a file name because it’s a GUID. With a simple PowerShell script, you can include the name of the job in the file name. If the job’s name needs to be changed, you can simply use the script to update it. The same reasoning applies to Job Steps’ names.
Using the function Get-SQLInstance, defined to initialize and login to a SQL Server instance that was introduced in previous articles, you can use the connection to simply iterate through a SMO Job Steps collection for each job and change the OutputFilename property.
However, you don’t usually want to apply such a change to all jobs, but rather to only a subset of them. This is where PowerShell pipe filtering comes in handy.
For example, you may have a set of jobs doing regular maintenance assigned to the Job Category “Database Maintenance.” In this category, you may have a subset of jobs create by Team Foundation Server for which you don’t want to apply the changes. the names of these jobs start with the substring “tfs.” The following code will configure all other enabled Job Steps to log to files with the given naming convention:
C:TempAgentLogsTest_Logging_$(ESCAPE_NONE(STRTDT))_$(ESCAPE_NONE(STRTTM))_Step $(ESCAPE_NONE(STEPID))_Update_Stats_AdventureWorks2008R2.txt
You can find the complete solution in the Script-P1-Change-Job-Steps-Logging.
Note these facts about the code:
-
the filename is assigned with a single string value, although for readability, you are concatenating different string fragments;
-
the string value is built replacing placeholders in PowerShell with run-time values; the form $(object.property) is needed when you want to access an object property;
-
another Agent token named STEPID, which contains the ordinal number of the Job Step inside a job, has been introduced;
-
because you don’t have a token for both the job and Job Step names, these are provided by PowerShell;
-
another requirement for the naming convention has been introduced. You are replacing all spaces in the file name with underscore; this is just an example and is not mandatory.
Also note that invoking the Alter method on the Job Step object commits the changes.
In this example, the job is named “Test Logging” and has a single Job Step named “Update Stats AdventureWorks2008R2,” so the final file name configured by the script is:
By executing the job, the output generated will be written to a file named according to our template, for example, the file Test Logging – 20110412104857 – Update Stats AdventureWorks2008R2.txt in the directory C:TempAgentLogs. The script for the resulting job can be found here: Script-J5-Test-Logging-Job-final
Conclusion
In this article, we have shown how SQL Server Agent logging works and how SQL Server Agent tokens can be used to customize filenames when Job Steps are logging to the file system. In addition, we have shown how PowerShell can easily apply changes on many jobs and job steps in one operation.
Please remember to be careful whenever doing such operations on production systems; in particular, be aware that you may already have multiple jobs in the “Database Maintenance” category that are used in the examples.
We’d love to hear from you—especially about which topics you’d like us to cover next
Download the full article: Logging-with-SQL-Server-Agent
Co-author
[avatar user=”halbert” size=”thumbnail” align=”left”]Since SQL Server 6.0 Albert worked as a consultant, a database developer and trainer and holds several Microsoft certifications including MCT. [/avatar]
