MSSQL Jobs überwachen - zu lange Laufzeiten

Hallo,

aktuell erstelle ich eine Anwendung zur aktiven Überwachung von MSSQL Jobs. Die Erkennung des letzten Ausführungsstatus eines Jobs ist einfach. Wie erkenne ich jedoch ob ein Job zu lange läuft?

Die Abfrage der Job-Informationen wird mit der System-Prozedur "sp_Help_job" unter Angabe des Job-Namen ausgeführt.

Leider werden erst nach Beendigung eines Jobs der Zeitpunkt der letzten und nächsten Ausführung gesetzt. Habe ich nun einen Job der minütlich laufen soll und der Job läuft aber allein schon 5 Minuten möchte ich das erkennen. Problem ist aber, dass ich zwar erkennen kann ob der Job läuft (execution_status) aber nicht ob er zu lang läuft, da ich ja nicht erkenne wann er gestartet wurde so lang er läuft.

Hat jemand eine Idee, wie ich da am besten herangehen könnte?

Ich würde jetzt einfach mal raten und Dir sagen, dass Du auch die Job Activity (sysjobactivity ) Tabelle brauchst.
Dort sind ja die Jobs gelistet, die aktiv sind.

Die aktuell laufenden Jobs bekommst du über die sysjobactivity Tabelle.

Z.B. alle laufenden Jobs mit Startzeitpunkt:

SELECT t1.job_id,
       t1.name,
       t2.start_execution_date
FROM msdb.dbo.sysjobs t1
    INNER JOIN msdb.dbo.sysjobactivity t2 ON t2.job_id = t1.job_id
WHERE t2.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
      AND t2.start_execution_date IS NOT NULL
      AND t2.stop_execution_date IS NULL
ORDER BY t2.run_requested_date ASC;

Damit erfahre ich ja auch nur, dass der Job gerade läuft. Aber nicht, ob er längst fertig sein sollte und erneut gestartet worden sein sollte.

Möglicherweise mache ich es mir auch zu kompliziert und es wäre einfacher zusätzlich für den jeweiligen Job in der Konfiguration zu hinterlegen wie lang er maximal laufen sollte.

Woher weisst du dann, dass der Job zu lange läuft?
Ist das ein Erfahrungswert oder wie entscheidest du aktuell, dass der Job zu lange läuft.

Na schon über Erfahrungswerte. Bei z.B: einem Job der neue Daten alle 5 Minuten aus einer Tabelle in eine andere schaufeln soll, weiß man ja wie lang das ungefähr dauern dürfte. Man kennt ja das Datenaufkommen und kann sich damit herleiten wie lange es dauert.

Wenn z.B. 100 Datensätze pro 5 Minuten kommen sind das entsprechend 1 Select und ggf. 100 Inserts/Updates. Nehmen wir an das Select dauert 1 Sekunde und je Insert noch mal 1 Sekunde kommen wir auf 101 Sekunden (Fiktive Beispielwerte). - Wenn der Job nun meinetwegen 10 Minuten läuft wissen wir ja das irgendwo etwas faul ist.

// EDIT: Eine alternative Lösung wäre über die Historie die durchschnittliche Laufzeit zu ermitteln und anhand dieser den Grenzwert zu definieren.

Meiner Erfahrung nach, wirst du über die (zu erwartende) Datenmengen nicht an Laufzeiten kommen.

Das klappt oft nur bei simplen Sachen. Wie z.B. einem simplen Insert. Weil das Nadelöhr ist da z.B. I/O und wenn es ein vernünftiges Storagekonzept ist, sollte die Performance relativ stabil sein.

Mach das doch erst einmal über eine Tabelle. Hinterleg den Job mit der maximalen Laufzeit dort.
Die joinst du mit meinen Statement und schon bekommst du alle Jobs mit zu langer Laufzeit.

In einem zweiten Schritt könntest du dann z.B. deine Tabelle, in Betracht der durchschnittlichen Laufzeiten eines vernünftig gewählten Zeitraums, aktualisieren.