Script to determine verbose logging options of SQL Server Jobs
We have many SQL Server instances in our enterprise with several hundreds of jobs across all servers created by many resources from the past and present each with a unique style of verbose logging options. Now, we are standardizing the verbose logging options to always append to a file and is there a way to figure out the current logging options of each job by a script instead of manually looking up all the job steps?
It is always recommended to configure the SQL Server job steps to output more verbose messages. This helps in troubleshooting, in case a job step failed and the verbose message might help in resolving the error quickly. If you haven't heard of verbose options for SQL Server Jobs then as a first step read the following tips: Verbose SQL Server Agent Logging and How to store longer SQL Agent Job Step output messages. Following standards while setting up new jobs and job steps will help the operations team to resolve the errors quickly and will save a lot of time and effort.
The possible options for verbose logging are:
- overwriting the existing output file,
- appending to the output file,
- write job step output to step history,
- write log to table by overwriting existing history
- and finally write log to table by appending to existing history.
Before diving into the script, lets take a look at some of these options to understand how they appear while configuring the job steps using the UI.
Now that we have a context of what the verbose options are and how they appear in the UI, let's take a look at the script to figure out the verbose options of all jobs on an instance.
The script uses a nested CTE approach along with bit masking and string concatenation using the blackbox XML approach in case there are multiple options set for the job step. Note that this problem can be solved in a variety of methods and this post outlines one approach. Comments and other solutions are welcome.
;WITH Flags (FlagID, FlagValue)
SELECT 0 AS FlagID, 'Overwrite output file' AS FlagValue UNION ALL
SELECT 2 AS FlagID, 'Append to output file' AS FlagValue UNION ALL
SELECT 4 AS FlagID, 'Write Transact-SQL job step output to step history' AS FlagValue UNION ALL
SELECT 8 AS FlagID, 'Write log to table (overwrite existing history)' UNION ALL
SELECT 16 AS FlagID, 'Write log to table (append to existing history)'
JobsInfo (Job_Name, Jobstep_ID, Jobstep_Name, Flags)
j.name as [Job_Name]
, js.step_name as [Jobstep_Name]
, js.step_id as [Jobstep_ID]
FROM msdb.dbo.sysjobsteps js JOIN msdb.dbo.sysjobs j
ON js.job_id = j.job_id
FinalData (Job_Name, JobStep_Name, [Jobstep_ID], FlagValue)
FROM JobsInfo JI CROSS JOIN Flags F
WHERE JI.Flags & F.FlagID <> 0
, ISNULL(STUFF (( SELECT ', ' + FD2.FlagValue FROM FinalData FD2
WHERE FD2.Job_Name = FD1.Job_Name AND FD2.Jobstep_Name = FD1.Jobstep_Name
ORDER BY ', ' + FD2.FlagValue FOR XML PATH('')), 1, 1, ' '), 'Overwrite output file') AS OptionsSet
FROM FinalData FD1 RIGHT OUTER JOIN JobsInfo JI
ON FD1.Job_Name = JI.Job_Name AND FD1.Jobstep_Name = JI.Jobstep_Name
ORDER BY Job_Name, Jobstep_Name
Here is a screenshot of the output from the above query from a test machine. This query might help to look at the verbose options of all the job steps on an instance.
Following standards is very important for an organization when multiple resources are developing solutions. Standards will help the operations team to manage their tasks easily and configuring verbose options will help to understand and troubleshoot the problem quickly. The above script outlines the currently configured verbose options of all SQL Server jobs & job steps on an instance and helps make appropriate changes quickly.
- Read these other tips related to SQL Agent Job logging
Last Updated: 2010-04-27
About the author
View all my tips