29 julio 2009

Accessing SQL Server Agent Jobs Metadata

In every BI project that we develop SQL Server Integration Services (SSIS) packages, we also need to create some SQL Server Agent jobs to automate the package execution. In addition, according to business needs, we also need to configure different “execution schedules” for the same SQL Agent job. For example, we have an SSIS package that needs to run from Monday to Friday at 1am (first schedule), and from Saturday and Sunday at 6am (second schedule). In other cases you can have more complex schedules like the one shown on the image below:

image

In this context, we needed an “easy way” to get better manageability and visibility of the existing job execution schedules for our packages (using jobs) instead of using SQL Server Management Studio to open job by job and reviewing its execution schedule. So I decided to query SQL Server Agent (SSA) jobs metadata directly. All the information about SQL Agent jobs (and its other features), is maintained in the MSDB system database, specifically in tables whose names begins with “dbo.sysjobs…”.

You can execute the following query in SQL Server Management Studio:

USE msdb
GO

SELECT name
FROM sys.tables
WHERE name like 'sysjob%'

The results are: image 

Here is a brief description of each of these tables from the SQL Server documentation in MSDN:



* sysjobs: Stores the information for each scheduled job to be executed by SQL Server Agent. This table is stored in the msdb database.


* sysjobactivity: Records current SQL Server Agent job activity and status.


* sysjobhistory: Contains information about the execution of scheduled jobs by SQL Server Agent. This table is stored in the msdb database.


* sysjobschedules: Contains schedule information for jobs to be executed by SQL Server Agent. This table is stored in the msdb database.


* sysjobsservers: Stores the association or relationship of a particular job with one or more target servers.This table is stored in the msdb database.


* sysjobssteps: Contains the information for each step in a job to be executed by SQL Server Agent. This table is stored in the msdb database.


* sysjobstepslogs: Contains the job step log for all SQL Server Agent job steps that are configured to write job step output to a table. This table is stored in the msdb database.



A SQL Server Diagram with the SQL Server Agent job tables and columns is shown below:



image



So, at this point it is relatively easy to create a query to get all the schedules for my active jobs:



SELECT J.name,  J.enabled, J.description, S.enabled AS 'Schedule Enabled?' , 
S.active_start_date, S.active_start_time, S.active_end_date,
S.active_end_time, S.freq_type, S.freq_interval,
S.freq_subday_type, S.freq_subday_interval
FROM sysjobs J
INNER JOIN sysjobschedules JS ON J.job_id = JS.job_id
INNER JOIN sysschedules S ON JS.schedule_id = S.schedule_id
WHERE J.enabled = 1
AND J.name LIKE '%NAPS%'
ORDER BY J.name



Most of the columns are self explanatory but those related to the schedule details are not (freq_type, freq_interval, freq_subday, freq_subday_interval). To get more information about what each of those column values means refer to the sysschedules table documentation on MSDN.



Finally, to make my SQL Agent job schedules results more relevant, I added some CASE conditions to translate values for frequency related columns as shown below:



'Frequency' = CASE S.freq_type 
WHEN 1 THEN 'One Time'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
ELSE 'Other'
END,
'Interval' = CASE
WHEN S.freq_type = 8 AND S.freq_interval = 1 THEN 'Sunday'
WHEN S.freq_type = 8 AND S.freq_interval = 2 THEN 'Monday'
WHEN S.freq_type = 8 AND S.freq_interval = 4 THEN 'Tuesday'
WHEN S.freq_type = 8 AND S.freq_interval = 8 THEN 'Wednesday'
WHEN S.freq_type = 8 AND S.freq_interval = 16 THEN 'Thursday'
WHEN S.freq_type = 8 AND S.freq_interval = 32 THEN 'Friday'
WHEN S.freq_type = 8 AND S.freq_interval = 64 THEN 'Saturday'
WHEN S.freq_type = 8 AND S.freq_interval = 127 THEN 'Everyday'
WHEN S.freq_type = 8 AND S.freq_interval = 111 THEN 'Except Thursday'
WHEN S.freq_type = 8 AND S.freq_interval = 95 THEN 'Except Friday'
WHEN S.freq_type = 8 AND S.freq_interval = 94 THEN 'Except Friday and Sunday'
WHEN S.freq_type = 8 AND S.freq_interval = 65 THEN 'Weekends'
WHEN S.freq_type = 8 AND S.freq_interval = 30 THEN 'Monday to Thursday'
WHEN S.freq_type = 4 THEN CAST(S.freq_interval AS varchar(10))+ ' day(s)'
WHEN S.freq_type = 16
THEN 'Day of the month = ' + CAST(S.freq_interval AS varchar(10))
WHEN S.freq_type = 32 AND S.freq_interval = 1 THEN 'Sunday'
WHEN S.freq_type = 32 AND S.freq_interval = 2 THEN 'Monday'
WHEN S.freq_type = 32 AND S.freq_interval = 3 THEN 'Tuesday'
WHEN S.freq_type = 32 AND S.freq_interval = 4 THEN 'Wednesday'
WHEN S.freq_type = 32 AND S.freq_interval = 5 THEN 'Thursday'
WHEN S.freq_type = 32 AND S.freq_interval = 6 THEN 'Friday'
WHEN S.freq_type = 32 AND S.freq_interval = 7 THEN 'Saturday'
WHEN S.freq_type = 32 AND S.freq_interval = 8 THEN 'Day'
WHEN S.freq_type = 32 AND S.freq_interval = 9 THEN 'Weekday'
WHEN S.freq_type = 32 AND S.freq_interval = 10 THEN 'Weekend day'
WHEN S.freq_type = 8 THEN CAST(S.freq_interval AS varchar(10))
ELSE 'N/A'
END,
'Sub Day Frequency' = CASE S.freq_subday_type
WHEN 1 THEN 'At the specified time'
WHEN 2 THEN 'Seconds'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END,
'Sub Day Interval' = S.freq_subday_interval


To put the cherry on the cake, for better access and automation you can include this query in your Reporting Services report as I did it:



image



The queries created here where designed for SQL Server 2008 but I’m very sure that it’s very similar for SQL Server 2005.


I have shown you how easy is to access SQL Server Agent metadata to get more visibility for IT operations and better maintenance purposes. Remember that there are more tables in MSDB for accessing other SQL Server Agent features.


Alan

2 comentarios:

Logan dijo...
Este comentario ha sido eliminado por un administrador del blog.
Anónimo dijo...

Amiable brief and this post helped me alot in my college assignement. Thanks you for your information.