Übersicht über alle Jobs und deren Ausführungen

Details
Kategorie: T-SQL

Das vorliegende Skript erstellt eine gespeicherte Prozedur namens sp_job_run (keine Parameter). Sie ruft Informationen über alle SQL Server Agent Jobs aus den Systemtabellen der msdb-Datenbank ab.

 

Funktion der Prozedur

Die Prozedur liefert für jeden Job eine Ergebniszeile mit folgenden Spalten:

 
 
Spalte Beschreibung
Categorie Name der Job-Kategorie (z. B. „Datenbankwartung“, „Berichte“)
JobName Name des SQL Agent Jobs
Enabled Aktivierungsstatus des Jobs („Yes“ / „No“)
OwnerName Eigentümer des Jobs (Anmeldename aus master.dbo.syslogins)
EMail Name des E-Mail-Operators, der bei Jobfehlern benachrichtigt wird
Description Vom Benutzer vergebene Job-Beschreibung
RunDate Datum und Uhrzeit der letzten Ausführung (Format: YYYY/MM/DD HH:MM:SS)
ExecutionStatus Status der letzten Ausführung: Failed, Succeded, Retry, Cancelled, In Progress oder unknown
StepDuration Dauer der letzten Ausführung (falsch berechnet – siehe unten)
Next Nächstes geplantes Ausführungsdatum und -uhrzeit (beliebig ausgewählt, wenn mehrere Zeitpläne existieren)

Die Ausgabe wird nach der Spalte Categorie

CREATE OR ALTER PROC dbo.sp_job_run
AS
BEGIN
    SET NOCOUNT ON;

    WITH LastRun AS (
        SELECT 
            job_id,
            run_date,
            run_time,
            run_status,
            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  -- Nur Job-Ausführungen, nicht einzelne Schritte
    ),
    NextRun AS (
        SELECT 
            job_id,
            next_run_date,
            next_run_time,
            ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY next_run_date, next_run_time) AS rn
        FROM msdb.dbo.sysjobschedules
        WHERE next_run_date > 0 
          AND (next_run_date > CAST(CONVERT(CHAR(8), GETDATE(), 112) AS INT)
               OR (next_run_date = CAST(CONVERT(CHAR(8), GETDATE(), 112) AS INT) 
                   AND next_run_time > CAST(REPLACE(CONVERT(CHAR(8), GETDATE(), 108), ':', '') AS INT)))
    )
    SELECT 
        cat.name AS 'Categorie',
        j.name AS 'JobName',
        CASE WHEN j.Enabled = 0 THEN 'No' ELSE 'Yes' END AS Enabled,
        l.name AS 'OwnerName',
        o.name AS 'EMail',
        j.description AS Description,
        -- RunDate (letzte Ausführung)
        CONVERT(CHAR(10), CAST(STR(lr.run_date, 8, 0) AS varchar), 111) + ' ' +
        STUFF(STUFF(RIGHT('000000' + CAST(lr.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS RunDate,
        -- ExecutionStatus
        CASE lr.run_status
            WHEN 0 THEN 'Failed'
            WHEN 1 THEN 'Succeeded'
            WHEN 2 THEN 'Retry'
            WHEN 3 THEN 'Cancelled'
            WHEN 4 THEN 'In Progress'
            ELSE 'unknown'
        END AS ExecutionStatus,
        -- StepDuration (korrekt als HH:MM:SS)
        RIGHT('00' + CAST(lr.run_duration / 10000 AS varchar), 2) + ':' +
        RIGHT('00' + CAST((lr.run_duration % 10000) / 100 AS varchar), 2) + ':' +
        RIGHT('00' + CAST(lr.run_duration % 100 AS varchar), 2) AS StepDuration,
        -- Next (nächste geplante Ausführung)
        CASE 
            WHEN nr.next_run_date IS NOT NULL AND nr.next_run_date > 0 
            THEN CONVERT(CHAR(10), CAST(STR(nr.next_run_date, 8, 0) AS varchar), 111) + ' ' +
                 STUFF(STUFF(RIGHT('000000' + CAST(nr.next_run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
            ELSE NULL
        END AS Next
    FROM msdb.dbo.sysjobs j
    INNER JOIN master.dbo.syslogins l ON j.owner_sid = l.sid
    LEFT JOIN msdb.dbo.syscategories cat ON j.category_id = cat.category_id
    LEFT JOIN msdb.dbo.sysoperators o ON j.notify_email_operator_id = o.id
    LEFT JOIN LastRun lr ON j.job_id = lr.job_id AND lr.rn = 1
    LEFT JOIN NextRun nr ON j.job_id = nr.job_id AND nr.rn = 1
    ORDER BY cat.name, j.name;
END;
GO

 

Übersicht über alle Jobs und deren Ausführungen

 

 

Main Menu