Script to determine verbose logging options of SQL Server Jobs

By:   |   Updated: 2010-04-27   |   Comments (4)   |   Related: More > SQL Server Agent

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.

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.

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.

It is always recommended to configure the SQL Server job steps to output more verbose messages

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 (FlagIDFlagValue
AS
(
   
SELECT AS FlagID'Overwrite output file' AS FlagValue UNION ALL
   
SELECT AS FlagID'Append to output file' AS FlagValue UNION ALL
   
SELECT AS FlagID'Write Transact-SQL job step output to step history' AS FlagValue UNION ALL
   
SELECT 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_NameJobstep_IDJobstep_NameFlags)
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_NameJobStep_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 <> 
)
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('')), 11' '), '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_NameJobstep_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.

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.

References

Next Steps


Last Updated: 2010-04-27


get scripts

next tip button



About the author
MSSQLTips author Sankar Reddy Sankar Reddy is a Database Engineer/DBA and SQL Server MVP. He has been working with SQL Server since 2003 in a variety of roles.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, June 24, 2019 - 6:04:53 AM - MikeTeeVee Back To Top

Thank you for your script.

It pointed me in the general direction, but I think it may be flawed, overcomplicated, and not as easy to query and reuse the results.

I used this instead:

SELECT J.name[JobName], JS.step_id[StepID], JS.step_name[StepName],
       JS.output_file_name[OutputFileName],
       (CASE WHEN JS.output_file_name IS NOT NULL AND (JS.flags &  2) = 0 THEN 'Overwrite' --"Append Output to Existing File" is Not-Checked.
             WHEN JS.output_file_name IS NOT NULL AND (JS.flags &  2) > 0 THEN 'Append' --"Append Output to Existing File" is Checked.
             ELSE NULL 
        END)[OutputFile], --When Null, then there is no OutputFileName.
       (CASE WHEN (JS.flags & 16) > 0 THEN 'Append' --"Append Output to Existing Entry in Table" is Checked.
             WHEN (JS.flags & 8)  > 0 THEN 'Overwrite'--"Log to Table" is Checked and "Append..." is Not-Checked.
             ELSE NULL 
        END)[LogTable], --When Null, then "Log to Table" is Not-Checked
       (CASE WHEN (JS.flags &  4) > 0 THEN 1 
             ELSE 0 
        END)[StepHistory] --"Include Step Output in History".  Use like a Bit/Boolean Flag.
FROM [msdb].dbo.sysjobs as J (nolock)
JOIN [msdb].dbo.sysjobsteps as JS (nolock)
  ON JS.job_id = J.job_id
ORDER BY J.name, JS.step_id

- MikeTeeVee


Wednesday, March 08, 2017 - 5:23:50 AM - steve sofar Back To Top

 Hello again

I added the output_file_name in the script to get a complete overview of verbose options set for each step.

Rgds

 

;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)' AS FlagValue UNION ALL

SELECT 16 AS FlagID, 'Write log to table (append to existing history)' AS FlagValue UNION ALL

SELECT 32 AS FlagID, 'Include step output in history'

),

JobsInfo (Job_Name, Jobstep_ID, Jobstep_Name, Flags, output_file_name)

AS

(

SELECT 

   j.name as [Job_Name]

   , js.step_name as [Jobstep_Name]

   , js.step_id as [Jobstep_ID]

   , flags

   , js.output_file_name 

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, output_file_name)

AS

(

SELECT 

   Job_Name

   , Jobstep_Name

   , [Jobstep_ID]

   , F.FlagValue

   , output_file_name

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

   , JI.output_file_name

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

 

 


Wednesday, March 08, 2017 - 4:33:58 AM - steve sofar Back To Top

Hello Sankar

Your script is very nice , give a quick display of verbose option set for every single step of a job

Pretty usefull  

Thanks a lot

 


Tuesday, June 08, 2010 - 2:07:23 PM - steve.schneider Back To Top

Very nice code! 

I adjusted the first CTE by adding one more select on the end so:

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)' AS FlagValue UNION ALL

SELECT 16 AS FlagID, 'Write log to table (append to existing history)' AS FlagValue UNION ALL

SELECT 32 AS FlagID, 'Include step output in history'

 NOte the last line...

By the way, do you know what the upper two bits stand for? 

Also, where did you find what any of the bits stood for?

And, ... if there was an "output file"...where could you go to find that (programmatically) of course!



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools