Problem: I came across a requirement to generate a list of jobs for specific databases along with their configuration detail like description, steps, schedules and latest history.
Solution: Simple solution for me is to create an excel sheet use a simple query to return jobs name and copy paste their detail one by one but I have to do this task daily or on demand. Now it is bit different I have searched for its solution and find some good articles most effective of all articles is
when I ran its query it shows jobs current running status with some useful information and this query is very good in some conditions but I have to do something more to fulfill my requirements.
Now I have decided to write query that run every useful information about job, its detail and history etc. to do this I have started writing my query using following steps.
Step1:
I have generated a Create script for an existing job and note which steps SQL Server perform to generate a job. I have found following procedures used from msdb database
sp_add_category
sp_add_job
sp_add_jobstep
sp_add_jobschedule
There are more procedures but these 4 are of my concerns.
Step2:
Now I have used sp_helptext procedure to return structure of every procedure and note tables when all procedure inserts its data. I have drilled down following tables from all above procedures
msdb.dbo.sysjobs
msdb.dbo.syscategories
msdb.dbo.sysjobsteps
msdb.dbo.sysjobschedules
msdb.dbo.sysschedules
Step3:
After that I have my required table and their columns. I have write my required query but now I need to understand columns data for many columns like job status and time interval based columns. For that I have gathered all required information from http://msdn.microsoft.com/en-us/library/ms174997.aspx online help and apply case statements to generate all required information.
Step4:
Once I have completed my initial work and execute query I got all required information but missed one important info and that is latest job history. For that my friend’s blog I have mentioned above helped me and I got another table
msdb.dbo.sysjobhistory
Conclusion:
Finally my query returns all my requried information about all jobs for specific databases along with their detail like description, steps, schedules and latest history.
Query:
Here is the query that I use. You can use where clause to limit your databases.
use msdb
go
; with jobHistory
as
(
SELECT row_number() over(partition by job_id order by instance_id desc) as JobExecCount,
job_id,step_name,[message],run_date,run_time,run_duration,run_status FROM msdb.dbo.sysjobhistory
)
SELECT j.name,j.enabled,description,
CASE c.category_class
WHEN 1 THEN 'JOB'
WHEN 2 THEN 'ALERT'
WHEN 3 THEN 'OPERATOR'
ELSE 'NONE'
END ,
CASE c.category_type
WHEN 1 THEN 'LOCAL'
WHEN 2 THEN 'MULTI-SERVER'
ELSE 'NONE'
END
, c.name
,step_id,
JS.step_name,
subsystem,
command,
CASE flags
WHEN 0 THEN 'Normal'
WHEN 1 THEN 'Encrypted command (read only)'
WHEN 2 THEN 'Append output files (if any)'
WHEN 4 THEN 'Write TSQL step output to step history'
WHEN 8 THEN 'Write log to table (overwrite existing history)'
WHEN 16 THEN 'Write log to table (append to existing history)'
WHEN 32 THEN 'Write all output to job history'
WHEN 64 THEN 'Create a Windows event to use as a signal for the Cmd jobstep to abort'
ELSE 'NONE'
END AS flags,
server,
database_name,
database_user_name,
CASE on_success_action
WHEN 1 THEN 'Quit With Success'
WHEN 2 THEN 'Quit With Failure'
WHEN 3 THEN 'Goto Next Step'
WHEN 4 THEN 'Goto Step'
ELSE ''
END AS on_success_action,
CASE on_fail_action
WHEN 1 THEN 'Quit With Success'
WHEN 2 THEN 'Quit With Failure'
WHEN 3 THEN 'Goto Next Step'
WHEN 4 THEN 'Goto Step'
ELSE ''
END AS on_fail_action,
ch.name as ScheduleName,
ch.enabled as ScheduleEnabe,
CASE freq_type
WHEN 1 THEN 'One time only'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly, relative to freq_interval'
WHEN 64 THEN 'Runs when the SQL Server Agent service starts'
WHEN 128 THEN 'Runs when the computer is idle'
ELSE 'NONE' END as freq_type,
CASE WHEN freq_type = 8 THEN
CASE freq_interval
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 4 THEN 'Tuesday'
WHEN 8 THEN 'Wednesday'
WHEN 16 THEN 'Thursday'
WHEN 32 THEN 'Friday'
WHEN 64 THEN 'Saturday'
ELSE 'NONE' END
WHEN freq_type = 8 THEN
CASE freq_interval
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'Day'
WHEN 9 THEN 'Weekday'
WHEN 10 THEN 'Weekend day'
ELSE 'NONE' END
ELSE '' END AS freq_interval,
CASE freq_subday_type
WHEN 1 THEN 'At the specified time'
WHEN 2 THEN 'Seconds'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END AS freq_subday_type,
freq_subday_interval, -- Number of freq_subday_type periods to occur between each execution of the job.
CASE freq_relative_interval
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 4 THEN 'Third'
WHEN 8 THEN 'Fourth'
WHEN 16 THEN 'Last'
END AS freq_relative_interval,
freq_recurrence_factor, -- Number of weeks or months between the scheduled execution of a job
active_start_date as ActiveAt, -- Date on which execution of a job can begin. The date is formatted as YYYYMMDD. NULL indicates today's date.
active_end_date, -- Date on which execution of a job can stop. The date is formatted YYYYMMDD.
active_start_time, -- Time on any day between active_start_date and active_end_date that job begins executing. Time is formatted HHMMSS, using a 24-hour clock.
active_end_time, -- Time on any day between active_start_date and active_end_date that job stops executing. Time is formatted HHMMSS, using a 24-hour clock.
JS.step_name,
[message],
run_date, -- Date the job or step started execution. For an In Progress history, this is the date/time the history was written.
run_time, -- Time the job or step started.
run_duration, -- Elapsed time in the execution of the job or step in HHMMSS format.
CASE run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
END AS run_status
FROM
msdb.dbo.sysjobs j LEFT OUTER JOIN
msdb.dbo.syscategories c ON j.category_id = c.category_id left outer join
msdb.dbo.sysjobsteps js ON js.job_id = j.Job_ID
left outer join msdb.dbo.sysjobschedules jch ON j.Job_ID= jch.Job_ID
inner join msdb.dbo.sysschedules ch on ch.schedule_id= jch.schedule_id
left outer join (select * from jobHistory where JobExecCount = 1 ) jH on jh.Job_ID = j.Job_ID
No comments:
Post a Comment