Script to determine verbose logging options of SQL Server Jobs

Problem
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?

Solution
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.

This information is stored as a bit pattern in msdb.dbo.sysjobsteps table in the flags column. Some of the values that go into the flags column are outlined in sp_add_jobstep.

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) 

AS

(

   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)

AS

(

SELECT 

   j.name as [Job_Name]

   , js.step_name as [Jobstep_Name]

   , js.step_id as [Jobstep_ID]

   , flags 

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)

AS

(

SELECT 

   Job_Name

   , Jobstep_Name

   , [Jobstep_ID]

   , F.FlagValue

FROM JobsInfo JI CROSS JOIN Flags F 

WHERE JI.Flags & F.FlagID <> 0 

)

SELECT DISTINCT 

   JI.Job_Name

   , JI.[Jobstep_ID]

   , JI.Jobstep_Name

   , 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.

Recap
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.