[QUESTION] Failed Job Alerts

[vpnicholls]

Which component is your question about?

Lite

Performance Monitor Version

2.4.1

Is your question about how it works, or the results?

Contributing / development

What's your question?

The long Running Jobs alert is very useful. Were there ever plans to also have alerts for failed jobs?

Additional Context

All versions with SQL Agent jobs.

[erikdarlingdata]

@vpnicholls sure, this would be a great addition. Did you want to contribute it?

[vpnicholls]

Maybe not this one for now. Possibly #748, though...

[khanberg]

@vpnicholls Who would you define a long running Job ?

[vpnicholls]

Hi @khanberg . Long running job alerts have been implemented in the tool already. I really like the statistical approach taken: 95th percentile and n x average, where n defaults to 3 but is configurable.

My question above, though, is about failed job alerts.

[MariusNe87]

+1 for this request

[erikdarlingdata]

+1 for this request

Did you want to contribute code for it?

[erikdarlingdata]

Implementation Guide: Failed Job Alerts

This feature would follow the same pattern as the existing long-running jobs feature. Here is a file-by-file breakdown of what is needed and how to test it. Starting with Lite first (our standard approach), then porting to Dashboard.

---

1. SQL Collection Query

Create Lite/Services/RemoteCollectorService.FailedJobs.cs (partial class, same pattern as RemoteCollectorService.RunningJobs.cs).

The query should hit msdb.dbo.sysjobhistory for recent failures:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT
    job_name       = j.name,
    job_id         = CONVERT(varchar(36), j.job_id),
    job_enabled    = j.enabled,
    step_id        = jh.step_id,
    step_name      = jh.step_name,
    failure_time   = msdb.dbo.agent_datetime(jh.run_date, jh.run_time),
    run_duration_seconds =
        (jh.run_duration / 10000) * 3600 +
        ((jh.run_duration / 100) % 100) * 60 +
        (jh.run_duration % 100),
    error_message  = jh.message,
    retries        = jh.retries_attempted,
    sql_severity   = jh.sql_severity
FROM msdb.dbo.sysjobhistory AS jh
JOIN msdb.dbo.sysjobs AS j
  ON j.job_id = jh.job_id
WHERE jh.run_status = 0  /* Failed */
AND   msdb.dbo.agent_datetime(jh.run_date, jh.run_time) >=
      DATEADD(MINUTE, -@lookback_minutes, GETDATE())
ORDER BY
    msdb.dbo.agent_datetime(jh.run_date, jh.run_time) DESC
OPTION(RECOMPILE);

Key differences from running jobs:

• Queries sysjobhistory (historical), not sysjobactivity (current)
• Filters on run_status = 0 (failed) instead of looking for active sessions
• Captures step_name, error_message, sql_severity — the useful failure context
• Uses a lookback window (e.g., since last collection) instead of "currently running"
• @lookback_minutes should match the collector frequency (default 5 min) plus a small buffer

2. DuckDB Schema

Add to Lite/Database/Schema.cs (follow the CreateRunningJobsTable pattern at line 576):

public const string CreateFailedJobsTable = @"
CREATE TABLE IF NOT EXISTS failed_jobs (
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    job_name VARCHAR NOT NULL,
    job_id VARCHAR NOT NULL,
    job_enabled BOOLEAN NOT NULL,
    step_id INTEGER NOT NULL,
    step_name VARCHAR NOT NULL,
    failure_time TIMESTAMP NOT NULL,
    run_duration_seconds BIGINT NOT NULL,
    error_message VARCHAR,
    retries_attempted INTEGER NOT NULL,
    sql_severity INTEGER NOT NULL
)";

public const string CreateFailedJobsIndex = @"
CREATE INDEX IF NOT EXISTS idx_failed_jobs_time
ON failed_jobs(server_id, collection_time)";

Also register in:

• DuckDbInitializer — call the CREATE TABLE/INDEX in the schema init method
• ArchivableTables list (line ~119 in Schema.cs) for Parquet archiving
• CreateArchiveViewsAsync() to generate v_failed_jobs

3. Data Model

Create Lite/Services/LocalDataService.FailedJobs.cs (partial class, mirrors LocalDataService.RunningJobs.cs):

public class FailedJobRow
{
    public DateTime CollectionTime { get; set; }
    public string JobName { get; set; } = "";
    public string JobId { get; set; } = "";
    public bool JobEnabled { get; set; }
    public int StepId { get; set; }
    public string StepName { get; set; } = "";
    public DateTime FailureTime { get; set; }
    public long RunDurationSeconds { get; set; }
    public string? ErrorMessage { get; set; }
    public int RetriesAttempted { get; set; }
    public int SqlSeverity { get; set; }

    public string FailureTimeLocal =>
        FailureTime.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss");
    public string DurationFormatted => FormatDuration(RunDurationSeconds);

    private static string FormatDuration(long seconds)
    {
        if (seconds < 60) return $"{seconds}s";
        if (seconds < 3600) return $"{seconds / 60}m {seconds % 60}s";
        return $"{seconds / 3600}h {(seconds % 3600) / 60}m";
    }
}

Methods to implement:

• GetFailedJobsAsync(int serverId) — latest snapshot for the grid
• GetRecentFailedJobCountAsync(int serverId) — for alert threshold check

4. Alert Logic

Add to the alert loop in Lite/MainWindow.xaml.cs (after the long-running job block at ~line 1631):

/* Failed job alerts */
if (App.AlertFailedJobEnabled && _dataService != null)
{
    var failedJobs = await _dataService.GetRecentFailedJobCountAsync(summary.ServerId);
    if (failedJobs.Count > 0)
    {
        // Fire tray notification for the most recent failure
        // Follow the same cooldown + mute pattern as long-running jobs
        // MetricName = "Failed Job"
    }
}

Alert settings to add in App.xaml.cs (follow the pattern at line ~90):

• AlertFailedJobEnabled (default: true)

Settings UI in Lite/Windows/SettingsWindow.xaml and .xaml.cs:

• Add a checkbox for enabling/disabling failed job alerts (follow the long-running job checkbox pattern)

5. UI — DataGrid

Add a "Failed Jobs" tab/grid on the server tab in Lite/Controls/ServerTab.xaml (follow the RunningJobsGrid pattern at line ~1535).

Suggested columns:

Column	Binding	Notes
Job Name	JobName
Step	StepName
Failed At	FailureTimeLocal
Duration	DurationFormatted	How long the step ran before failing
Error	ErrorMessage	Wrap or truncate
Severity	SqlSeverity
Retries	RetriesAttempted

6. Analysis Engine (optional but nice)

Add a FAILED_JOBS fact in Lite/Analysis/DuckDbFactCollector.cs (follow the RUNNING_JOBS pattern at line ~1181) and score it in Lite/Analysis/FactScorer.cs (line ~243).

---

Files to Create (Lite)

File	Purpose
Lite/Services/RemoteCollectorService.FailedJobs.cs	SQL query + DuckDB insert
Lite/Services/LocalDataService.FailedJobs.cs	Read methods + data model

Files to Modify (Lite)

File	What to Change
Lite/Database/Schema.cs	Add table + index constants, register in archivable tables
Lite/Database/DuckDbInitializer.cs	Call CREATE TABLE/INDEX during init, add archive view
Lite/App.xaml.cs	Add AlertFailedJobEnabled config property
Lite/MainWindow.xaml.cs	Add alert check in the alert loop (~line 1631)
Lite/Controls/ServerTab.xaml	Add Failed Jobs DataGrid
Lite/Controls/ServerTab.xaml.cs	Add refresh method, wire up tab
Lite/Windows/SettingsWindow.xaml	Add alert checkbox
Lite/Windows/SettingsWindow.xaml.cs	Wire up checkbox to config

Dashboard Port (after Lite is validated)

The Dashboard version follows the same pattern but uses SQL Server storage:

• Create install/52_collect_failed_jobs.sql (collector stored procedure, follow 51_collect_running_jobs.sql)
• Add collect.failed_jobs table in install/02_create_tables.sql
• Add report.failed_jobs view in install/48_create_additional_reporting_views.sql
• Add Dashboard/Models/FailedJobItem.cs
• Add grid to Dashboard/ServerTab.xaml
• Register in CollectorScheduleWindow.xaml.cs with defaults

---

Testing Checklist

• Collection: Add a server with SQL Agent jobs, verify failed_jobs table populates after a collection cycle
• No msdb access: Verify the collector handles servers without msdb gracefully (Azure SQL DB, restricted permissions)
• Grid display: Failed jobs show in the UI with correct formatting
• Alert fires: Intentionally fail a job, confirm tray notification appears
• Alert cooldown: Confirm repeated failures don't spam notifications
• Alert muting: Mute a specific job, confirm it suppresses
• Settings toggle: Disable the alert in Settings, confirm it stops firing
• Empty state: Server with no failed jobs shows appropriate empty message
• Upgrade path: Existing installations get the new table via upgrade script (not just fresh installs)
• Archive: Verify Parquet archiving works for the new table

[erikdarlingdata]

Implementation Guide: Dashboard (Full) — Failed Job Alerts

The previous comment covers Lite. This comment covers the Dashboard, which has significantly more moving parts: SQL install scripts, a collector stored procedure, a reporting view, schedule registration, a C# data model, a DatabaseService fetch method, a UI grid, and an MCP tool.

Use the existing running jobs feature as the template for every file below.

---

1. Collection Table

Add to install/02_create_tables.sql (follow the collect.running_jobs block at line 1379):

/*
Failed SQL Agent Jobs
Captures recently failed SQL Agent job steps for alerting and review
*/
IF OBJECT_ID(N'collect.failed_jobs', N'U') IS NULL
BEGIN
    CREATE TABLE
        collect.failed_jobs
    (
        collection_id bigint IDENTITY NOT NULL,
        collection_time datetime2(7) NOT NULL
            DEFAULT SYSDATETIME(),
        job_name sysname NOT NULL,
        job_id uniqueidentifier NOT NULL,
        job_enabled bit NOT NULL,
        step_id int NOT NULL,
        step_name sysname NOT NULL,
        failure_time datetime2(7) NOT NULL,
        run_duration_seconds bigint NOT NULL,
        error_message nvarchar(4000) NULL,
        retries_attempted int NOT NULL DEFAULT 0,
        sql_severity int NOT NULL DEFAULT 0,
        CONSTRAINT
            PK_failed_jobs
        PRIMARY KEY CLUSTERED
            (collection_time, collection_id)
        WITH
            (DATA_COMPRESSION = PAGE)
    );

    PRINT 'Created collect.failed_jobs table';
END;

2. Collector Stored Procedure

Create install/52_collect_failed_jobs.sql (follow install/51_collect_running_jobs.sql exactly for structure):

ALTER PROCEDURE
    collect.failed_jobs_collector
(
    @debug bit = 0
)
WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    -- Same error handling pattern as running_jobs_collector:
    -- 1. Gate: check msdb.dbo.sysjobs exists (skip on Azure SQL DB)
    -- 2. Ensure collect.failed_jobs table exists
    -- 3. Query msdb.dbo.sysjobhistory for failures since last collection
    -- 4. Log to config.collection_log

    -- Core query: failures since the last successful collection
    ;WITH
        last_collection AS
    (
        SELECT
            last_time = MAX(fj.collection_time)
        FROM collect.failed_jobs AS fj
    )
    INSERT INTO
        collect.failed_jobs
    (
        collection_time,
        job_name,
        job_id,
        job_enabled,
        step_id,
        step_name,
        failure_time,
        run_duration_seconds,
        error_message,
        retries_attempted,
        sql_severity
    )
    SELECT
        collection_time = @start_time,
        job_name = j.name,
        job_id = j.job_id,
        job_enabled = j.enabled,
        step_id = jh.step_id,
        step_name = jh.step_name,
        failure_time = msdb.dbo.agent_datetime(jh.run_date, jh.run_time),
        run_duration_seconds =
            (jh.run_duration / 10000) * 3600 +
            ((jh.run_duration / 100) % 100) * 60 +
            (jh.run_duration % 100),
        error_message = jh.message,
        retries_attempted = jh.retries_attempted,
        sql_severity = jh.sql_severity
    FROM msdb.dbo.sysjobhistory AS jh
    JOIN msdb.dbo.sysjobs AS j
      ON j.job_id = jh.job_id
    CROSS JOIN last_collection AS lc
    WHERE jh.run_status = 0  /* Failed */
    AND   msdb.dbo.agent_datetime(jh.run_date, jh.run_time) >
          ISNULL(lc.last_time, DATEADD(MINUTE, -10, SYSDATETIME()))
    ORDER BY
        msdb.dbo.agent_datetime(jh.run_date, jh.run_time) DESC
    OPTION(RECOMPILE);
END;

Important: The lookback uses last_collection time so failures are never missed or double-counted, regardless of collector frequency.

3. Reporting View

Add to install/48_create_additional_reporting_views.sql (follow the report.running_jobs pattern at line 229):

CREATE OR ALTER VIEW
    report.failed_jobs
AS
WITH
    latest_snapshot AS
(
    SELECT TOP (1)
        latest_time = fj.collection_time
    FROM collect.failed_jobs AS fj
    ORDER BY
        fj.collection_time DESC
)
SELECT
    fj.collection_time,
    fj.job_name,
    fj.job_id,
    fj.job_enabled,
    fj.step_id,
    fj.step_name,
    fj.failure_time,
    fj.run_duration_seconds,
    duration_formatted =
        RIGHT(N'00' + CONVERT(nvarchar(10), fj.run_duration_seconds / 3600), 2) + N':' +
        RIGHT(N'00' + CONVERT(nvarchar(10), (fj.run_duration_seconds % 3600) / 60), 2) + N':' +
        RIGHT(N'00' + CONVERT(nvarchar(10), fj.run_duration_seconds % 60), 2),
    fj.error_message,
    fj.retries_attempted,
    fj.sql_severity
FROM collect.failed_jobs AS fj
JOIN latest_snapshot AS ls
  ON ls.latest_time = fj.collection_time;

4. Schedule Registration

install/04_create_schedule_table.sql — Add a row for the new collector (follow the running_jobs_collector row at line 77):

(N'failed_jobs_collector', 1, 5, 2, 30, N'Recently failed SQL Agent job steps with error details'),

Also add to the RDS incompatibility block (line ~128) since msdb.dbo.syssessions is inaccessible on RDS:

N'failed_jobs_collector'

install/42_scheduled_master_collector.sql — Register the collector so the master scheduler calls it.

Dashboard/CollectorScheduleWindow.xaml.cs — Add to all three preset profiles (follow the running_jobs_collector entries):

Profile	Frequency
Aggressive (line ~58)	2 min
Balanced (line ~90)	5 min
Low-Impact (line ~122)	30 min

5. C# Data Model

Create Dashboard/Models/FailedJobItem.cs (follow Dashboard/Models/RunningJobItem.cs):

public class FailedJobItem
{
    public string JobName { get; set; } = string.Empty;
    public Guid JobId { get; set; }
    public bool JobEnabled { get; set; }
    public int StepId { get; set; }
    public string StepName { get; set; } = string.Empty;
    public DateTime FailureTime { get; set; }
    public long RunDurationSeconds { get; set; }
    public string DurationFormatted { get; set; } = string.Empty;
    public string? ErrorMessage { get; set; }
    public int RetriesAttempted { get; set; }
    public int SqlSeverity { get; set; }
}

6. DatabaseService Fetch Method

Add to Dashboard/Services/DatabaseService.Overview.cs (follow GetRunningJobsAsync() at line 277):

public async Task<List<FailedJobItem>> GetFailedJobsAsync()
{
    var items = new List<FailedJobItem>();
    // Query report.failed_jobs
    // Map SqlDataReader to FailedJobItem
    // Order by failure_time DESC
    return items;
}

7. UI — DataGrid

Add a "Failed Jobs" TabItem in Dashboard/ServerTab.xaml (follow the "Running Jobs" tab at line 353):

Suggested columns:

Column	Binding	Width
Job Name	JobName	*
Step	StepName	150
Failed At	FailureTime	160
Duration	DurationFormatted	80
Error	ErrorMessage	*
Severity	SqlSeverity	70
Retries	RetriesAttempted	60

Wire up in Dashboard/ServerTab.xaml.cs (follow the RunningJobsDataGrid pattern at line 2771).

8. MCP Tool

Create Dashboard/Mcp/McpFailedJobTools.cs (follow Dashboard/Mcp/McpJobTools.cs):

[McpServerTool(Name = "get_failed_jobs"),
 Description("Gets recently failed SQL Agent jobs with error details.")]
public static async Task<string> GetFailedJobs(
    ServerManager serverManager,
    DatabaseServiceRegistry registry,
    [Description("Server name or display name.")] string? server_name = null)
{
    // Same resolve + serialize pattern as GetRunningJobs
    // Return: server, failed_job_count, jobs array
}

9. Upgrade Script

Create upgrades/{current}-to-{next}/ with:

1. 01_create_failed_jobs_table.sql — the CREATE TABLE from step 1
2. 02_create_failed_jobs_collector.sql — the stored procedure from step 2
3. 03_create_failed_jobs_view.sql — the reporting view from step 3
4. 04_register_failed_jobs_schedule.sql — INSERT into config.collection_schedule
5. upgrade.txt — list the scripts in order

This is critical — existing installations will not have the table/procedure/view. The upgrade script must be idempotent (IF NOT EXISTS checks on everything).

---

Files to Create (Dashboard)

File	Purpose
install/52_collect_failed_jobs.sql	Collector stored procedure
Dashboard/Models/FailedJobItem.cs	C# data model
Dashboard/Mcp/McpFailedJobTools.cs	MCP tool for Claude integration
upgrades/{ver}/01-04_*.sql	Upgrade path for existing installs

Files to Modify (Dashboard)

File	What to Change
install/02_create_tables.sql	Add collect.failed_jobs table definition
install/04_create_schedule_table.sql	Register collector + RDS exclusion
install/42_scheduled_master_collector.sql	Register in the master scheduler loop
install/48_create_additional_reporting_views.sql	Add report.failed_jobs view
Dashboard/CollectorScheduleWindow.xaml.cs	Add to Aggressive/Balanced/Low-Impact presets
Dashboard/Services/DatabaseService.Overview.cs	Add GetFailedJobsAsync() method
Dashboard/ServerTab.xaml	Add Failed Jobs DataGrid tab
Dashboard/ServerTab.xaml.cs	Wire up refresh + data binding

---

Testing Checklist (Dashboard-specific)

• Fresh install: Run full install, verify collect.failed_jobs table + report.failed_jobs view + collect.failed_jobs_collector procedure all exist
• Upgrade path: Start from v2.6.0, run upgrade scripts, verify the same objects are created
• Collector execution: Run EXEC collect.failed_jobs_collector @debug = 1 and verify output
• No msdb (Azure SQL DB): Verify collector logs SKIPPED and does not error
• RDS exclusion: Verify collector is auto-disabled on RDS environments
• Reporting view: Verify SELECT * FROM report.failed_jobs returns correctly formatted data
• UI grid: Failed jobs tab shows data, sorts by failure_time DESC
• MCP tool: Call get_failed_jobs via Claude and verify JSON output
• Schedule presets: Verify all three presets (Aggressive/Balanced/Low-Impact) include the new collector
• Retention: Verify data older than retention period gets cleaned up by the existing purge job
• Empty state: Server with no failed jobs shows "no data" message in the grid