SQL Server Agent Jobs: How to Know About Failures Before the Business Does

The most expensive SQL Server Agent job failure is the one you find out about from a user at 8 AM asking why their report is empty. The second most expensive is the one you find out about from your manager at 9 AM asking why last night’s ETL did not load. The least expensive, by far, is the one you found at 2:05 AM because your alerting fired and you resolved it before anyone else was awake.

SQL Agent jobs are the silent workhorses of every SQL Server environment. They run backups, execute ETL pipelines, rebuild indexes, update statistics, purge old data, and generate reports. They run unattended, at odd hours, with no human watching. When they fail, the failure is logged, a job history entry is written, and depending on your alerting configuration, either you receive an immediate notification or nobody finds out until the downstream impact surfaces.

This article covers the five most common reasons Agent jobs fail, the exact queries experienced DBAs run first when investigating a failure, how to build alerting that fires before the business notices, and how to move from reactive firefighting to proactive monitoring that catches problems before they become incidents.

1 How SQL Agent Job History Actually Works Beginner

Before investigating a failure it helps to understand where SQL Agent stores its data. Everything about job executions lives in the msdb database in three tables: msdb.dbo.sysjobs (one row per job), msdb.dbo.sysjobsteps (one row per step per job), and msdb.dbo.sysjobhistory (one row per execution per step, plus one summary row per job execution).

The history table has a limit. By default SQL Server retains 1,000 rows per job and a maximum of 10,000 rows total across all jobs. When those limits are hit, older history is deleted automatically. On a busy server with many frequent jobs this means history may only go back days rather than weeks. You can increase these limits in SQL Agent Properties but the history is still stored in msdb which should not grow unbounded. The right solution for long-term trend analysis is a separate job history table that you populate from sysjobhistory on a schedule.

-- Check current job history retention settings
USE msdb;

-- These settings are in SQL Agent Properties (right-click SQL Agent in SSMS)
-- You can also view them from the registry but the SSMS UI is easier

-- How many history rows are currently stored
SELECT COUNT(*) AS TotalHistoryRows
FROM msdb.dbo.sysjobhistory;

-- History rows per job (to see which jobs dominate history)
SELECT
    j.name                          AS JobName,
    COUNT(h.instance_id)            AS HistoryRows,
    MIN(CAST(
        CAST(h.run_date AS CHAR(8)) + ' ' +
        STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
        AS DATETIME))               AS OldestEntry,
    MAX(CAST(
        CAST(h.run_date AS CHAR(8)) + ' ' +
        STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
        AS DATETIME))               AS MostRecentEntry
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobhistory h ON h.job_id = j.job_id
GROUP BY j.job_id, j.name
ORDER BY HistoryRows DESC;

2 Failure Mode 1: Disk Space Beginner

Disk space is the most common cause of overnight job failures and the easiest to prevent with proactive monitoring. A backup job that ran successfully every night for a year can fail the moment the backup volume fills up. Log file autogrowth can consume the last available space on a volume. TempDB growth during an index rebuild job can exhaust a shared data volume. In each case the failure is abrupt, the error message is clear, and the root cause is obvious in retrospect but was preventable with alerting.

-- Check disk space on all volumes SQL Server can see
SELECT DISTINCT
    vs.volume_mount_point,
    vs.logical_volume_name,
    vs.file_system_type,
    vs.total_bytes / 1073741824.0   AS TotalGB,
    vs.available_bytes / 1073741824.0 AS FreeGB,
    CAST((vs.available_bytes * 100.0) / vs.total_bytes AS DECIMAL(5,1))
                                    AS FreePct,
    CASE
        WHEN (vs.available_bytes * 100.0) / vs.total_bytes < 10
        THEN 'CRITICAL: Under 10% free'
        WHEN (vs.available_bytes * 100.0) / vs.total_bytes < 20
        THEN 'WARNING: Under 20% free'
        ELSE 'OK'
    END                             AS SpaceStatus
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
ORDER BY FreePct ASC;

-- Check recent auto growth events (default trace)
DECLARE @tracepath NVARCHAR(500);
SELECT @tracepath = path FROM sys.traces WHERE is_default = 1;

SELECT
    DB_NAME(t.DatabaseID)           AS DatabaseName,
    t.FileName,
    CASE t.EventClass
        WHEN 92 THEN 'Data File Auto Grow'
        WHEN 93 THEN 'Log File Auto Grow'
    END                             AS GrowthType,
    t.IntegerData * 8 / 1024        AS GrowthMB,
    t.StartTime
FROM sys.fn_trace_gettable(@tracepath, DEFAULT) t
WHERE t.EventClass IN (92, 93)
AND   t.StartTime >= DATEADD(DAY, -7, GETDATE())
ORDER BY t.StartTime DESC;

3 Failure Mode 2: Credential and Password Changes Beginner

SQL Agent jobs often run under contexts other than the SQL Server service account. Proxy accounts let job steps execute with specific Windows credentials for accessing file shares, running SSIS packages, or executing PowerShell scripts. When those proxy account credentials change, every job step using that proxy silently breaks.

The failure message is usually clear: “The step failed” followed by an access denied error or login failure. But the cause is easy to miss if you are not aware that proxy accounts exist in your environment.

-- List all proxy accounts and their associated credentials
USE msdb;

SELECT
    p.name                          AS ProxyName,
    p.credential_id,
    c.name                          AS CredentialName,
    c.credential_identity,          -- the Windows account
    p.enabled,
    p.description
FROM msdb.dbo.sysproxies p
JOIN sys.credentials c ON c.credential_id = p.credential_id
ORDER BY p.name;

-- Find all job steps that use a proxy
SELECT
    j.name                          AS JobName,
    s.step_id,
    s.step_name,
    s.subsystem,
    p.name                          AS ProxyUsed,
    c.credential_identity           AS RunsAs
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobsteps s ON s.job_id = j.job_id
LEFT JOIN msdb.dbo.sysproxies p ON p.proxy_id = s.proxy_id
LEFT JOIN sys.credentials c ON c.credential_id = p.credential_id
WHERE s.proxy_id IS NOT NULL
ORDER BY j.name, s.step_id;

-- Linked server logins that may fail after password changes
SELECT
    ls.name                         AS LinkedServerName,
    ll.uses_self_credential,
    ll.remote_name                  AS RemoteLoginName
FROM sys.servers ls
LEFT JOIN sys.linked_logins ll ON ll.server_id = ls.server_id
WHERE ls.is_linked = 1
ORDER BY ls.name;

4 Failure Mode 3: Network and External Dependencies Beginner

Many Agent jobs depend on external systems: source databases for ETL, file shares for backup destinations, FTP endpoints for data exchange, linked servers for cross-instance queries, SMTP servers for Database Mail. Any of these can be unavailable at the moment the job runs without being permanently broken.

Network dependency failures are characterized by timeout errors, connection refused messages, or “server not found” errors in the job step output. They are often intermittent which makes them harder to diagnose than a permanent credential failure.

-- Test a linked server connection from within SQL Server
-- Useful for diagnosing linked server timeout failures
SELECT TOP 1 * FROM [YourLinkedServer].[RemoteDatabase].[dbo].[SomeTable];

-- Check if a specific linked server is responding
EXEC sys.sp_testlinkedserver @servername = N'YourLinkedServerName';

-- Review linked server timeout and connection settings
SELECT
    name,
    product,
    provider,
    data_source,
    connect_timeout,
    query_timeout,
    is_remote_login_enabled,
    is_remote_proc_transaction_promotion_enabled
FROM sys.servers
WHERE is_linked = 1;

-- Jobs that failed with network-related error messages
SELECT TOP 20
    j.name                          AS JobName,
    h.step_id,
    h.step_name,
    CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
    END                             AS Status,
    h.message,
    CAST(
        CAST(h.run_date AS CHAR(8)) + ' ' +
        STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
        AS DATETIME)                AS RunDateTime
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE h.run_status = 0  -- failed steps only
AND (
    h.message LIKE '%timeout%'
    OR h.message LIKE '%cannot connect%'
    OR h.message LIKE '%network%'
    OR h.message LIKE '%not found%'
    OR h.message LIKE '%access denied%'
)
ORDER BY h.run_date DESC, h.run_time DESC;

5 Failure Mode 4: Blocking and Long-Running Jobs Beginner

A job that has run successfully every night for months is not immune to failure from blocking. As data volumes grow, an index rebuild that used to complete in 30 minutes now takes 90 minutes. The maintenance window that was designed for 60 minutes is now exceeded. The next scheduled run starts before the previous one completes. Two instances of the same job are now running simultaneously, contending for the same resources, potentially deadlocking each other or causing the next business day’s data to be processed twice.

This failure mode is insidious because the job never technically fails until the overlap problem becomes severe enough to cause an error. In the meantime, runtime is gradually increasing and nobody notices because nobody is watching the trend.

-- Find jobs whose runtime has been increasing over the last 30 days
-- This is the early warning signal for the overlap failure mode

WITH JobRuntimes AS (
    SELECT
        j.name                          AS JobName,
        CAST(
            CAST(h.run_date AS CHAR(8)) + ' ' +
            STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
            AS DATETIME)                AS RunDateTime,
        -- Convert run_duration from HHMMSS integer to seconds
        (h.run_duration / 10000 * 3600) +
        ((h.run_duration % 10000) / 100 * 60) +
        (h.run_duration % 100)          AS DurationSeconds
    FROM msdb.dbo.sysjobhistory h
    JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
    WHERE h.step_id = 0  -- summary rows only (not individual steps)
    AND   h.run_status = 1  -- successful runs only
    AND   CAST(
            CAST(h.run_date AS CHAR(8)) + ' ' +
            STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
            AS DATETIME) >= DATEADD(DAY, -30, GETDATE())
)
SELECT
    JobName,
    COUNT(*)                        AS RunCount,
    MIN(DurationSeconds) / 60       AS MinDurationMin,
    AVG(DurationSeconds) / 60       AS AvgDurationMin,
    MAX(DurationSeconds) / 60       AS MaxDurationMin,
    -- Compare recent average to older average
    AVG(CASE WHEN RunDateTime >= DATEADD(DAY, -7,  GETDATE())
             THEN DurationSeconds END) / 60 AS Last7DaysAvgMin,
    AVG(CASE WHEN RunDateTime < DATEADD(DAY, -7,  GETDATE())
             THEN DurationSeconds END) / 60 AS Prior23DaysAvgMin
FROM JobRuntimes
GROUP BY JobName
HAVING COUNT(*) >= 5
ORDER BY
    (AVG(CASE WHEN RunDateTime >= DATEADD(DAY, -7, GETDATE())
              THEN DurationSeconds END) -
     AVG(CASE WHEN RunDateTime < DATEADD(DAY, -7, GETDATE())
              THEN DurationSeconds END)) DESC;
-- Jobs at the top have the largest runtime increase recently

6 Failure Mode 5: Silent Failures and Poor Error Handling Beginner

This is the most dangerous failure mode because the job reports success. A stored procedure raises an error internally. The job step is configured with "On failure: go to next step" or the stored procedure catches the error and swallows it without re-raising. The job step completes with a return code of 0 (success) and the job summary shows green. Business data is incomplete or incorrect and nobody finds out until a downstream process fails or a report shows wrong numbers.

-- THE PROBLEM: A stored procedure that silently fails
-- This is a common pattern in legacy code:

CREATE PROCEDURE dbo.usp_LoadDailyData
AS
BEGIN
    BEGIN TRY
        INSERT INTO dbo.DailyFact
        SELECT * FROM dbo.StagingTable;
    END TRY
    BEGIN CATCH
        -- Developer intended to log the error but the insert fails silently
        -- and the procedure returns 0 (success) anyway
        INSERT INTO dbo.ErrorLog (Message)
        VALUES (ERROR_MESSAGE());
        -- No THROW or RETURN 1 -- the error is swallowed
        -- SQL Agent sees success
    END CATCH
END

-- THE FIX: Always re-raise errors in stored procedures called by Agent jobs
CREATE PROCEDURE dbo.usp_LoadDailyData_Fixed
AS
BEGIN
    BEGIN TRY
        INSERT INTO dbo.DailyFact
        SELECT * FROM dbo.StagingTable;
    END TRY
    BEGIN CATCH
        -- Log the error
        INSERT INTO dbo.ErrorLog (Message, ErrorTime)
        VALUES (ERROR_MESSAGE(), SYSDATETIME());

        -- Then re-raise it so SQL Agent knows the step failed
        THROW;
        -- OR: RAISERROR(ERROR_MESSAGE(), 16, 1);
    END CATCH
END

-- Check job steps that might be hiding failures
-- Look for steps configured with non-default failure actions
SELECT
    j.name                          AS JobName,
    s.step_id,
    s.step_name,
    s.on_success_action,
    s.on_fail_action,
    CASE s.on_fail_action
        WHEN 1 THEN 'Quit job reporting failure (correct)'
        WHEN 2 THEN 'Quit job reporting success (DANGEROUS)'
        WHEN 3 THEN 'Go to next step'
        WHEN 4 THEN 'Go to step number ' + CAST(s.on_fail_step_id AS VARCHAR)
    END                             AS FailureAction
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobsteps s ON s.job_id = j.job_id
WHERE s.on_fail_action = 2  -- reports success on failure
OR    s.on_fail_action = 3  -- goes to next step on failure
ORDER BY j.name, s.step_id;

7 The First 5 Queries to Run When a Job Fails Intermediate

When you get a failure notification after hours, run these queries in order. They tell you what failed, when it failed, what the error was, and whether it has failed before.

-- ============================================================
-- QUERY 1: What failed and when?
-- Get the most recent failure for a specific job
-- ============================================================

SELECT TOP 10
    j.name                          AS JobName,
    h.step_id,
    h.step_name,
    CASE h.run_status
        WHEN 0 THEN 'FAILED'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
    END                             AS RunStatus,
    CAST(
        CAST(h.run_date AS CHAR(8)) + ' ' +
        STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
        AS DATETIME)                AS RunDateTime,
    (h.run_duration / 10000 * 3600) +
    ((h.run_duration % 10000) / 100 * 60) +
    (h.run_duration % 100)          AS DurationSeconds,
    h.message                       AS ErrorMessage
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE j.name = 'YourJobName'   -- replace with actual job name
ORDER BY h.run_date DESC, h.run_time DESC;

-- ============================================================
-- QUERY 2: Has this job been failing recently?
-- Last 30 runs with success/fail status
-- ============================================================

SELECT TOP 30
    j.name                          AS JobName,
    CASE h.run_status
        WHEN 0 THEN 'FAILED'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
    END                             AS RunStatus,
    CAST(
        CAST(h.run_date AS CHAR(8)) + ' ' +
        STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
        AS DATETIME)                AS RunDateTime,
    (h.run_duration / 10000 * 3600) +
    ((h.run_duration % 10000) / 100 * 60) +
    (h.run_duration % 100)          AS DurationSeconds
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE j.name   = 'YourJobName'
AND   h.step_id = 0  -- summary rows only
ORDER BY h.run_date DESC, h.run_time DESC;

-- ============================================================
-- QUERY 3: All currently running jobs
-- Is the job still running or did it finish?
-- ============================================================

SELECT
    j.name                          AS JobName,
    ja.start_execution_date,
    DATEDIFF(MINUTE, ja.start_execution_date, GETDATE())
                                    AS RunningMinutes,
    js.step_name                    AS CurrentStep,
    ja.last_executed_step_id
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs j ON j.job_id = ja.job_id
LEFT JOIN msdb.dbo.sysjobsteps js
    ON  js.job_id  = ja.job_id
    AND js.step_id = ja.last_executed_step_id
WHERE ja.session_id = (
    SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
AND ja.start_execution_date IS NOT NULL
AND ja.stop_execution_date IS NULL  -- still running
ORDER BY ja.start_execution_date;

-- ============================================================
-- QUERY 4: Disk space right now
-- Was disk the cause?
-- ============================================================

SELECT DISTINCT
    vs.volume_mount_point,
    vs.total_bytes / 1073741824.0   AS TotalGB,
    vs.available_bytes / 1073741824.0 AS FreeGB,
    CAST((vs.available_bytes * 100.0) / vs.total_bytes AS DECIMAL(5,1))
                                    AS FreePct
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
ORDER BY FreePct ASC;

-- ============================================================
-- QUERY 5: SQL Server error log at the time of failure
-- ============================================================

EXEC xp_readerrorlog 0, 1, NULL, NULL,
    '2026-06-05 02:00',  -- replace with actual failure time minus 5 min
    '2026-06-05 02:30',  -- replace with actual failure time plus 30 min
    N'desc';

8 Reading the Job Step Output File Intermediate

The error message stored in sysjobhistory is often truncated at 1,024 characters. For SSIS packages, PowerShell scripts, and complex multi-step jobs, the full error detail is in the output file configured on the job step. Always configure output files on critical job steps and know where they are before you need them during an after-hours incident.

-- Find the output file paths configured for all job steps
SELECT
    j.name                          AS JobName,
    s.step_id,
    s.step_name,
    s.subsystem,
    CASE WHEN s.output_file_name = ''
         THEN 'No output file configured'
         ELSE s.output_file_name
    END                             AS OutputFilePath,
    s.flags,
    -- Flag 2 means output is appended (not overwritten)
    CASE WHEN s.flags & 2 = 2
         THEN 'Append mode'
         ELSE 'Overwrite mode'
    END                             AS OutputMode
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobsteps s ON s.job_id = j.job_id
ORDER BY j.name, s.step_id;

-- Configure an output file on a critical job step
USE msdb;
EXEC sp_update_jobstep
    @job_name       = 'YourJobName',
    @step_id        = 1,
    @output_file_name = 'C:\SQLAgentLogs\YourJobName_Step1.log',
    @flags          = 2;  -- 2 = append to file (do not overwrite)
    -- Appending means you keep history across multiple runs

9 Setting Up Job Failure Alerts Beginner

SQL Agent has built-in alerting through the Operators and Alerts system. An operator is a person or group that receives notifications. An alert is a condition that triggers a notification. Connecting the two is how you ensure job failures generate immediate notifications rather than waiting for someone to check the history in the morning.

-- ============================================================
-- STEP 1: Create an operator (the person who gets notified)
-- ============================================================

USE msdb;
EXEC sp_add_operator
    @name                   = N'DBA Team',
    @enabled                = 1,
    @email_address          = N'dba-team@yourcompany.com',
    @weekday_pager_start_time = 90000,   -- 9 AM
    @weekday_pager_end_time   = 180000;  -- 6 PM

-- ============================================================
-- STEP 2: Configure a specific job to notify on failure
-- ============================================================

EXEC sp_update_job
    @job_name           = 'YourCriticalJobName',
    @notify_level_email = 2,            -- 2 = notify on failure
    @notify_email_operator_name = N'DBA Team';

-- notify_level values:
-- 1 = on success
-- 2 = on failure
-- 3 = on completion (success or failure)

-- ============================================================
-- STEP 3: Create an alert for ALL job failures
-- This catches any job that is not individually configured
-- ============================================================

EXEC sp_add_alert
    @name               = N'SQLYARD - Any SQL Agent Job Failure',
    @message_id         = 0,
    @severity           = 0,
    @enabled            = 1,
    @delay_between_responses = 300,  -- 5 minutes between repeat alerts
    @include_event_description_in = 1,
    @job_id             = N'00000000-0000-0000-0000-000000000000',
    @category_name      = N'[Uncategorized (Local)]';

EXEC sp_add_notification
    @alert_name     = N'SQLYARD - Any SQL Agent Job Failure',
    @operator_name  = N'DBA Team',
    @notification_method = 1;  -- 1 = email

-- ============================================================
-- STEP 4: Verify Database Mail is configured
-- Alerts depend on Database Mail for email delivery
-- Database Mail uses Service Broker internally
-- ============================================================

-- Check Database Mail is enabled and configured
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBA Alerts',          -- your mail profile name
    @recipients   = 'dba-team@yourcompany.com',
    @subject      = 'Test: Database Mail is working',
    @body         = 'If you receive this email, Database Mail is configured correctly.';

10 Runtime Trend Monitoring: Catching Problems Before They Fail Intermediate

A job that is gradually taking longer to complete is going to fail eventually. The data volume has grown, an index has fragmented, a stored procedure is doing more work than it used to. Watching runtime trends catches these problems weeks before they cause a failure. This is the difference between reactive and proactive Agent job management.

-- ============================================================
-- Build a runtime history table for trend analysis
-- Run this as a scheduled job daily to capture history
-- before it rolls off the default sysjobhistory retention
-- ============================================================

-- Create the history table (run once)
CREATE TABLE dbo.AgentJobRuntimeHistory (
    HistoryID       INT IDENTITY(1,1) PRIMARY KEY,
    JobName         NVARCHAR(256) NOT NULL,
    RunDateTime     DATETIME NOT NULL,
    DurationSeconds INT NOT NULL,
    RunStatus       TINYINT NOT NULL,
    StepID          INT NOT NULL,
    Message         NVARCHAR(4000),
    CapturedAt      DATETIME2 DEFAULT SYSDATETIME()
);

CREATE INDEX IX_AgentJobRuntimeHistory_JobDate
    ON dbo.AgentJobRuntimeHistory (JobName, RunDateTime DESC);

-- Daily job to copy new history records (run as a SQL Agent job)
INSERT INTO YourMonitoringDB.dbo.AgentJobRuntimeHistory
    (JobName, RunDateTime, DurationSeconds, RunStatus, StepID, Message)
SELECT
    j.name,
    CAST(
        CAST(h.run_date AS CHAR(8)) + ' ' +
        STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
        AS DATETIME),
    (h.run_duration / 10000 * 3600) +
    ((h.run_duration % 10000) / 100 * 60) +
    (h.run_duration % 100),
    h.run_status,
    h.step_id,
    h.message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE CAST(
    CAST(h.run_date AS CHAR(8)) + ' ' +
    STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
    AS DATETIME) > (SELECT ISNULL(MAX(RunDateTime), '2000-01-01')
                    FROM YourMonitoringDB.dbo.AgentJobRuntimeHistory
                    WHERE JobName = j.name);

-- Weekly runtime trend report: jobs getting slower
SELECT
    JobName,
    COUNT(*)                        AS RunsLast30Days,
    MIN(DurationSeconds) / 60       AS MinMinutes,
    AVG(DurationSeconds) / 60       AS AvgMinutes,
    MAX(DurationSeconds) / 60       AS MaxMinutes,
    AVG(CASE WHEN RunDateTime >= DATEADD(DAY, -7,  GETDATE())
             THEN DurationSeconds END) / 60.0 AS RecentWeekAvgMin,
    AVG(CASE WHEN RunDateTime < DATEADD(DAY, -7,  GETDATE())
             AND RunDateTime >= DATEADD(DAY, -30, GETDATE())
             THEN DurationSeconds END) / 60.0 AS PriorPeriodAvgMin,
    SUM(CASE WHEN RunStatus = 0 THEN 1 ELSE 0 END) AS FailureCount
FROM dbo.AgentJobRuntimeHistory
WHERE RunDateTime >= DATEADD(DAY, -30, GETDATE())
AND   StepID = 0  -- summary rows only
GROUP BY JobName
HAVING COUNT(*) >= 5
ORDER BY
    (AVG(CASE WHEN RunDateTime >= DATEADD(DAY, -7, GETDATE())
              THEN DurationSeconds END) -
     AVG(CASE WHEN RunDateTime < DATEADD(DAY, -7, GETDATE())
              AND RunDateTime >= DATEADD(DAY, -30, GETDATE())
              THEN DurationSeconds END)) DESC;

11 Job Overlap Detection Intermediate

A job that exceeds its scheduled interval starts the next run while the previous one is still running. Two instances of the same job running simultaneously can cause data to be processed twice, create locking contention, or exhaust connection pool limits.

-- Detect jobs that are currently running multiple instances
SELECT
    j.name                          AS JobName,
    COUNT(ja.job_id)                AS RunningInstances,
    MIN(ja.start_execution_date)    AS OldestStart,
    DATEDIFF(MINUTE, MIN(ja.start_execution_date), GETDATE())
                                    AS OldestRunMinutes
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs j ON j.job_id = ja.job_id
WHERE ja.session_id = (
    SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
AND ja.start_execution_date IS NOT NULL
AND ja.stop_execution_date IS NULL
GROUP BY j.name
HAVING COUNT(ja.job_id) > 1
ORDER BY OldestRunMinutes DESC;

-- Detect historical overlaps from job history
-- Jobs where a new run started before the previous run completed
WITH JobRuns AS (
    SELECT
        j.name                          AS JobName,
        CAST(
            CAST(h.run_date AS CHAR(8)) + ' ' +
            STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
            AS DATETIME)                AS StartTime,
        CAST(
            CAST(h.run_date AS CHAR(8)) + ' ' +
            STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
            AS DATETIME) +
        CAST(
            ((h.run_duration / 10000 * 3600) +
             ((h.run_duration % 10000) / 100 * 60) +
             (h.run_duration % 100)) AS FLOAT) / 86400.0
                                        AS EndTime
    FROM msdb.dbo.sysjobhistory h
    JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
    WHERE h.step_id = 0
    AND   h.run_status = 1
    AND   h.run_date >= CONVERT(INT, CONVERT(CHAR(8),
            DATEADD(DAY, -30, GETDATE()), 112))
)
SELECT
    a.JobName,
    a.StartTime                     AS Run1Start,
    a.EndTime                       AS Run1End,
    b.StartTime                     AS Run2Start,
    DATEDIFF(MINUTE, b.StartTime, a.EndTime)
                                    AS OverlapMinutes
FROM JobRuns a
JOIN JobRuns b
    ON  b.JobName   = a.JobName
    AND b.StartTime > a.StartTime
    AND b.StartTime < a.EndTime     -- Run 2 started before Run 1 ended
ORDER BY a.JobName, a.StartTime DESC;

12 The Weekly Agent Health Report Beginner

Run this query weekly and review the output. It is a complete snapshot of Agent job health across the environment and catches trends before they become incidents.

-- ============================================================
-- COMPLETE WEEKLY AGENT HEALTH REPORT
-- Save output and compare week over week
-- ============================================================

SELECT
    j.name                          AS JobName,
    j.enabled                       AS IsEnabled,
    CASE
        WHEN ja.start_execution_date IS NOT NULL
        AND  ja.stop_execution_date IS NULL
        THEN 'Currently Running'
        ELSE 'Not Running'
    END                             AS CurrentStatus,
    -- Last run outcome
    CASE last_h.run_status
        WHEN 0 THEN 'FAILED'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        ELSE 'Unknown'
    END                             AS LastRunStatus,
    CAST(
        CAST(last_h.run_date AS CHAR(8)) + ' ' +
        STUFF(STUFF(RIGHT('000000' + CAST(last_h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
        AS DATETIME)                AS LastRunDateTime,
    (last_h.run_duration / 10000 * 3600) +
    ((last_h.run_duration % 10000) / 100 * 60) +
    (last_h.run_duration % 100)     AS LastRunSeconds,
    -- Last 30 days statistics
    stats.RunCount30Days,
    stats.FailCount30Days,
    stats.AvgDurationSeconds30Days / 60
                                    AS AvgDurationMin30Days,
    stats.MaxDurationSeconds30Days / 60
                                    AS MaxDurationMin30Days,
    -- Next scheduled run
    CASE js.next_run_date
        WHEN 0 THEN 'Not scheduled'
        ELSE CAST(
            CAST(js.next_run_date AS CHAR(8)) + ' ' +
            STUFF(STUFF(RIGHT('000000' + CAST(js.next_run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
            AS CHAR(20))
    END                             AS NextScheduledRun
FROM msdb.dbo.sysjobs j
-- Most recent history entry
LEFT JOIN (
    SELECT job_id, run_status, run_date, run_time, run_duration,
           ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY run_date DESC, run_time DESC) AS rn
    FROM msdb.dbo.sysjobhistory
    WHERE step_id = 0
) last_h ON last_h.job_id = j.job_id AND last_h.rn = 1
-- 30-day statistics
LEFT JOIN (
    SELECT
        job_id,
        COUNT(*)                    AS RunCount30Days,
        SUM(CASE WHEN run_status = 0 THEN 1 ELSE 0 END)
                                    AS FailCount30Days,
        AVG((run_duration / 10000 * 3600) +
            ((run_duration % 10000) / 100 * 60) +
            (run_duration % 100))   AS AvgDurationSeconds30Days,
        MAX((run_duration / 10000 * 3600) +
            ((run_duration % 10000) / 100 * 60) +
            (run_duration % 100))   AS MaxDurationSeconds30Days
    FROM msdb.dbo.sysjobhistory
    WHERE step_id = 0
    AND   run_date >= CONVERT(INT, CONVERT(CHAR(8),
            DATEADD(DAY, -30, GETDATE()), 112))
    GROUP BY job_id
) stats ON stats.job_id = j.job_id
-- Currently running check
LEFT JOIN (
    SELECT job_id, start_execution_date, stop_execution_date
    FROM msdb.dbo.sysjobactivity
    WHERE session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
) ja ON ja.job_id = j.job_id
-- Next run schedule
LEFT JOIN msdb.dbo.sysjobschedules js ON js.job_id = j.job_id
WHERE j.enabled = 1  -- only active jobs
ORDER BY
    CASE WHEN last_h.run_status = 0 THEN 0 ELSE 1 END,  -- failures first
    stats.FailCount30Days DESC,
    j.name;

References

• Microsoft Docs: SQL Server Agent
• Microsoft Docs: msdb.dbo.sysjobhistory
• Microsoft Docs: msdb.dbo.sysjobactivity
• Microsoft Docs: msdb.dbo.sysproxies (Proxy Accounts)
• Microsoft Docs: sp_add_alert
• SQLYARD: Troubleshooting SSIS Package Failures in SQL Server Agent Jobs
• SQLYARD: SQL Server Blocking vs Deadlocks
• SQLYARD: SQL Server Service Broker Complete Guide
• SQLYARD: SQL Server Fragmentation Complete Guide
• SQLYARD: SQL Server Health Check Toolkit