Using Tokens to Create Dynamically Named Job Step Output Logs
There are situations that make me think I need to find and enroll in a “Making Better Google Searches” class. After some brief digging into Ola Halengren’s Maintenance Solution I inadvertently came across the way of creating log file names dynamically for job step output. While this may not sound all that wonderful, I’ve asked a lot of people how to do this over the years (including MS support during various incident resolutions) and no one has been able to tell me what it took 2 minutes of digging into Ola’s solution to find. I think the problem isn’t the folks I asked, but how I asked the question. Anyway, read on and I will show you the way to create dynamic output log names within a Job Step’s Advanced Tab.
What’s in a name?
There are no shortage of situations where it is handy to log the output of a job step in MSSQL. There are also no shortage of situations where you want to keep a running history of these executions. However, as I was previously unaware of how to create dynamic names, I resorted to giving my output logs static names:
Thankfully it’s quite easy to create dynamically generated output logs so you are not overwriting the output of the last job step execution. To do this you want to use Tokens as outlined in this MSDN Article. For example, putting $(ESCAPE_SQUOTE(STRTDT)) and $(ESCAPE_SQUOTE(STRTTM)) into the output log name…
…will generate output log files as follows:
Who knew it would be that easy?
Don’t Forget To Clean House
Just remember that by creating dynamically named output logs, you’re also going to have to come up with a way to clean up the old ones. Because these are not Maintenance Plan Text Reports, you cannot rely on a Maintenance plan to do this for you. If you are using Ola’s solution but are not logging to your default Log path or you are using a different log file format, you must add another step to the Output File Cleanup job. If you’re not using Ola’s solution, you will need to create a new job to maintain your log files. For the example above, here is the syntax (based off of Ola’s job) to clean up files older than 30 days by way of a Operating system (CmdExec) job step:
cmd /q /c "For /F "tokens=1 delims=" %v In ('ForFiles /P "C:\Path" /m *.log /d -30 2^>^&1') do if not "%v" == "ERROR: No files found with the specified search criteria." echo del "C:\Path"\%v& del "C:\Path"\%v"
Take note that if you’re running your Database Server on XP, the ForFiles command is not included so this will fail. However you can get it to run if you copy the ForeFiles.exe command over from a Windows 2003 server.
For those of you suffering from the same inability to properly Google the seemingly simple operation of creating job output logs with dynamic names, hopefully this helps, and a big thanks goes out to Ola.
John
John Eisbrener, DBA 