Problem: I have assigned a task to generate a list of jobs
for specific databases along with their 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