Monitoring SQL Server Jobs & its Schedules

229 views 16:45 0 Comments 22 May 2023

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_namejob_enabledschedule_namefreq_recurrence_factorfrequencyDaystimelast_execution_datetime [YYYYMMDDHHMM]

Leave a Reply

Your email address will not be published. Required fields are marked *