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.
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_NameHere 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.
References
- Some content in this article comes from BOL page on sp_add_jobstep and sp_help_jobstep.
Next Steps
- Read these other tips related to SQL Agent Job logging
Sankar is a Database Engineer/DBA currently working for FunMobility, a mobile media entertainment company. He has been working with SQL Server from 2003 in a variety of roles including development DBA, Database administrator, Report writer and datawarehouse developer. Currently he works with high traffic OLTP and Tera byte datawarehouse systems. Sankar spends most of his free time at MSDN social forums answering questions.
