Introduction
SQL Server jobs are essential for automating tasks and maintaining the stability and efficiency of a SQL Server system. These jobs can be scheduled to run at specific times to perform various operations. In this blog post, we will discuss a T-SQL script that helps manage and monitor SQL Server jobs with daily and weekly schedules. We will explain how to use the script and understand the results to effectively handle these jobs.
Analyzing SQL Server Jobs with Daily Schedules
The first part of the script focuses on jobs that run every day. It retrieves important information about these jobs, such as their names, whether they are enabled or disabled, the schedule they follow, how often they repeat, and the time of execution. It also shows the date and time of the last execution.
By using this script, you can easily identify SQL Server jobs that run daily and gather details about their execution patterns. The script uses system tables in the msdb database to retrieve this information.
Understanding the Results
The script provides a result set that includes the job name, its status (enabled or disabled), the schedule name, how often it repeats, the frequency (which is always “Daily”), the number of days between executions, the execution time, and the date and time of the last execution.
The execution time is shown based on the frequency and the sub-day interval. If the sub-day interval is in seconds, minutes, or hours, the script displays the corresponding time. If the sub-day interval is not specified, only the execution time is shown.
Monitoring SQL Server Jobs with Weekly Schedules
The second part of the script focuses on jobs that run on specific days of the week. Similar to the previous section, it retrieves information about these jobs, including their names, status, schedule names, recurrence factors, frequency (which is always “Weekly”), the days of the week they run on, the execution time, and the date and time of the last execution.
The script uses bitwise operators to determine the days of the week based on the frequency interval. Each day is represented by a specific bit, and the script checks these bits to identify the corresponding days.
Interpreting the Results
The result set gives an overview of jobs with weekly schedules. By analyzing this information, you can determine which jobs run on specific days, their execution times, and when they were last executed. This helps monitor job execution patterns and identify any potential issues.
Conclusion
Managing and monitoring SQL Server jobs is crucial for maintaining a healthy database system. The provided T-SQL script helps extract important information about jobs with daily and weekly schedules. By running this script, you can gain insights into job execution patterns, last execution dates, and other details. This allows for effective management and monitoring of SQL Server jobs, ensuring their optimal performance and minimizing disruptions.
Remember to regularly run this script and review the results to stay informed about the status and execution history of your SQL Server jobs. With this knowledge, you can proactively address any issues and maintain a reliable and efficient SQL Server environment.
TSQL
-- Jobs with a daily schedule SELECT sysjobs.name AS job_name, sysjobs.enabled AS job_enabled, sysschedules.name AS schedule_name, sysschedules.freq_recurrence_factor, 'Daily' AS frequency, 'every ' + CAST(freq_interval AS varchar(3)) + ' day(s)' AS Days, CASE WHEN freq_subday_type = 2 THEN ' every ' + CAST(freq_subday_interval AS varchar(7)) + ' seconds starting at ' + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS varchar(6)), 6), 3, 0, ':'), 6, 0, ':') WHEN freq_subday_type = 4 THEN ' every ' + CAST(freq_subday_interval AS varchar(7)) + ' minutes starting at ' + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS varchar(6)), 6), 3, 0, ':'), 6, 0, ':') WHEN freq_subday_type = 8 THEN ' every ' + CAST(freq_subday_interval AS varchar(7)) + ' hours starting at ' + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS varchar(6)), 6), 3, 0, ':'), 6, 0, ':') ELSE ' starting at ' + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS varchar(6)), 6), 3, 0, ':'), 6, 0, ':') END AS time, last_execution.last_execution_datetime AS last_execution_datetime FROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id INNER JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id LEFT JOIN ( SELECT job_id, MAX(CONVERT(BIGINT, run_date) * 1000000 + CONVERT(BIGINT, run_time)) AS last_execution_datetime FROM msdb.dbo.sysjobhistory WHERE step_id = 0 -- Considering only the last overall job execution GROUP BY job_id ) AS last_execution ON sysjobs.job_id = last_execution.job_id WHERE freq_type = 4 UNION -- Jobs with a weekly schedule SELECT sysjobs.name AS job_name, sysjobs.enabled AS job_enabled, sysschedules.name AS schedule_name, sysschedules.freq_recurrence_factor, 'Weekly' AS frequency, REPLACE( CASE WHEN freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END , ', ', '') AS Days, CASE WHEN freq_subday_type = 2 THEN ' every ' + CAST(freq_subday_interval AS varchar(7)) + ' seconds starting at ' + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS varchar(6)), 6), 3, 0, ':'), 6, 0, ':') WHEN freq_subday_type = 4 THEN ' every ' + CAST(freq_subday_interval AS varchar(7)) + ' minutes starting at ' + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS varchar(6)), 6), 3, 0, ':'), 6, 0, ':') WHEN freq_subday_type = 8 THEN ' every ' + CAST(freq_subday_interval AS varchar(7)) + ' hours starting at ' + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS varchar(6)), 6), 3, 0, ':'), 6, 0, ':') ELSE ' starting at ' + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS varchar(6)), 6), 3, 0, ':'), 6, 0, ':') END AS time, last_execution.last_execution_datetime AS last_execution_datetime FROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id INNER JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id LEFT JOIN ( SELECT job_id, MAX(CONVERT(BIGINT, run_date) * 1000000 + CONVERT(BIGINT, run_time)) AS last_execution_datetime FROM msdb.dbo.sysjobhistory WHERE step_id = 0 -- Considering only the last overall job execution GROUP BY job_id ) AS last_execution ON sysjobs.job_id = last_execution.job_id WHERE freq_type = 8 ORDER BY job_enabled DESC;
Result
job_name | job_enabled | schedule_name | freq_recurrence_factor | frequency | Days | time | last_execution_datetime [YYYYMMDDHHMM] |