Search This Blog & Web

Tuesday, April 17, 2012

SQL Agent Job Detail report (Information, Latest history)


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: