SQL-Server speichert umfassende Informationen zu Aufträgen – wie Laufzeit, Erfolg, nächste Ausführung etc. – in der Systemdatenbank msdb. Damit diese Informationen ausgewertet und verarbeitet werden können, müssen sie zunächst extrahiert werden. Dieser Blogbeitrag erklärt, wie solch eine Extraktion abläuft.

Viele Aufgaben, sei es der Datenimport, die nächtliche Datenaufbereitung oder die Versendung von Berichten mit dem DeltaMaster Publisher sollen regelmäßig zu bestimmten Zeiten ausgeführt werden. Dafür wird oft der im SQL Server enthaltene Scheduler verwendet – der SQL Server-Agent.
Die einzelnen Aufträge, die im SQL Server-Agent eingestellt sind, werden zu den im Auftrag definierten Zeiten automatisch gestartet und alle Schritte eines Auftrags nacheinander abgearbeitet. Das ist bekannt und funktioniert seit vielen Jahren zuverlässig.
Möchte man Daten aus den Aufträgen wie zum Beispiel das letzte Ausführungsdatum, das Ausführungsergebnis, das Datum der nächsten Ausführung oder andere Auftragsdetails in einer SQL-Abfrage ermitteln und ggf. in das vorhandene Datenmodell einbinden, so ist das über die Tabellen in der Systemdatenbank msdb möglich. Auch für eine Datenflussdokumentation können diese Informationen hilfreich sein.
In diesem Blogbeitrag soll gezeigt werden, welche Tabellen aus der msdb dafür verwendet werden können und Beispiele aufgezeigt werden, wie die Daten aus diesen Tabellen extrahiert werden können.

Tabellen

Die Systemdatenbank msdb enthält eine Vielzahl von Systemtabellen. Nicht alle enthalten Informationen zu den Aufträgen, die im SQL Server-Agent angelegt wurden. Die Tabellen, welche die Silbe „job“ im Namen enthalten, sind einfach zu finden, aber viele Tabellen enthalten diese Silbe nicht und enthalten trotzdem notwendige Informationen.
Darum soll am Anfang eine Auflistung der wichtigsten Tabellen in alphabetischer Reihenfolge zum Thema „Aufträge“ stehen:

Quelle: https://technet.microsoft.com/de-de/library/ms181367(v=sql.105).aspx

Beispiele

Auftragsinformationen abrufen

Erstellen wir uns dafür zwei Aufträge im SQL Server-Agent. Der eine soll Berichte aus unserer Demodatenbank Chair mit Hilfe des DeltaMaster Publishers per Mail versenden. Der andere soll der täglichen Datenaufbereitung der Demodatenbank Chair dienen. Den ersten Auftrag nennen wir „Chair Berichtsversand“, den zweiten Auftrag „Chair Datenaufbereitung“.
(Das Erstellen von Aufträgen im SQL Server-Agent wird als bekannt vorausgesetzt und ist daher nicht Bestandteil dieses Beitrags.)
Für die meisten ist sicher die Information, wann der Auftrag letztmalig gelaufen ist und ob er erfolgreich ausgeführt werden konnte, wichtig. Schließlich möchten wir wissen, ob die Berichte planmäßig versandt wurden und ob die Datenaufbereitung erfolgreich ausgeführt wurde und die Daten in unserem Modell damit aktuell sind.
Wenn einer der Aufträge einen Fehler produziert hat, sollte dieser ebenfalls mit ausgegeben werden.
Für diese Informationen kann folgende SQL-Abfrage ausgeführt werden:

Für die beiden selbst erstellten Aufträge und den Systemauftrag „syspolicy_purge_history“ ergibt diese Abfrage folgendes Ergebnis:

Abbildung 1: Daten zur Auftragsausführung

Alle Aufträge sind zuletzt erfolgreich ausgeführt worden. Der Auftrag „Chair Berichtsversand“ verfügt über keine weiteren Ausführungstermine, daher sind die Spalten für Datum und Zeit der nächsten Ausführung leer (NULL).
Konnte dagegen ein Auftrag nicht erfolgreich ausgeführt werden, ist die Spalte „Fehlermeldung“ mit dem Fehler des nicht ausführbaren Auftragsschritts gefüllt. Um eine Fehlermeldung zu provozieren, wurde beim Auftrag „Chair Berichtsversand“ eine nicht vorhandene JobId (im Beispiel die JobID = 17) an den Publisher übergeben und der Auftrag erneut ausgeführt.
Die Abfrage liefert daraufhin folgendes Ergebnis:

Abbildung 2: Daten zur Auftragsausführung mit Fehlermeldung

Die vollständige Fehlermeldung für diesen Fall lautet:
„Ausgeführt als Benutzer: “NT Service\SQLSERVERAGENT“. System.ApplicationException: Job 17 not found. bei DeltaMiner.Reporting.ReportServer.Run(Int32 jobID) bei Delta-Miner.Reporting.ReportServerMain.RunCommandLineNew() bei Delta-Miner.Reporting.ReportServerMain.OnLoad(EventArgs e). Prozessexitcode 1. Fehler bei Schritt.“
Nun kann man diese Informationen auch im Auftragsverlauf der jeweiligen Aufträge im SQL Server-Agent abrufen. Aber nicht jeder, den diese Informationen interessieren, hat darauf Zugriff. So könnte man das Ergebnis dieser Abfrage in einem neuen Auftrag per Mail versenden oder aber die Angaben in ein bestehendes DeltaMaster-Modell einbinden und z. B. als SQL-Durchgriff zur Verfügung stellen.

Datenflussdokumentation

Auch für eine Datenflussdokumentation lassen sich die in der msdb-Datenbank gespeicherten Auftragsinformationen sehr gut nutzen, da die Tabellen alle Informationen zu den Aufträgen enthalten, welche Daten aus Vorsystemen importieren und verarbeiten. So lässt sich darstellen, welche Daten mit welchem Auftrag zu welchen Zeiten importiert werden und wann und mit welchen Schritten die Daten verarbeitet werden.
Die allgemeinen Parameter eines Auftrages lassen sich mit folgender SQL-Abfrage ermitteln:

Der Systemauftrag „syspolicy_purge_history“ wurde hier bewusst ausgeschlossen, weil er für eine Datenflussdokumentation eher weniger von Interesse ist.

Das Ergebnis dieser Abfrage sieht für die beiden in 2.1. angelegten Aufträge wie folgt aus:

Abbildung 3: Daten zu den Aufträgen

Die Details zu den einzelnen Schritten, welche im Rahmen des jeweiligen Auftrages ausgeführt wer-den, kann folgende SQL-Abfrage selektieren:

Diese Abfrage liefert folgendes Ergebnis zurück:

Abbildung 4: Daten zu den Auftragsschritten

Nun fehlen für eine Datenflussdokumentation noch die Informationen, wann und wie häufig ein Auftrag ausgeführt wird – also die Details aus den jeweiligen Zeitplänen jedes Auftragsschritts.
Dafür kann man folgende SQL-Abfrage einsetzen:

Die Zeitpläne für Systemaufträge wurden hier wieder durch die WHERE-Bedingung entfernt.
Das Ergebnis sieht für den Auftrag „Datenaufbereitung Chair“ wie folgt aus:

Abbildung 5: Daten zum Auftragszeitplan

Der Auftrag „Chair Berichtsversand“ erscheint im Ergebnis nicht, da er in unserem Beispiel nicht über einen Zeitplan verfügt. Dieser Auftrag wird nicht automatisch zu bestimmten Zeiten ausgeführt.
Mit diesen drei SQL-Abfragen lassen sich die wichtigsten Daten zu den eingerichteten Aufträgen im SQL Server-Agent ermitteln und für eine Dokumentation nutzen.
Wer sich noch mehr mit diesem Thema beschäftigen möchte, dem sei folgender Link empfohlen: https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/

Fazit und Ausblick

Dieser Blogbeitrag zeigt, wie man die Informationen zu Aufträgen aus den Tabellen der Systemdatenbank msdb extrahiert. Wer Zugriff auf das SQL Managementstudio hat, kann diese Informationen direkt im Auftrag z. B. im Auftragsverlauf nachlesen. Aber für diejenigen, welche mit DeltaMaster arbeiten, aber nicht über einen Zugriff auf das SQL Managementstudio verfügen, bleiben diese Informationen verborgen.
Häufig wird zumindest die letzte erfolgreiche Aufbereitungszeit der Daten als Kennzahl im DeltaMaster dargestellt. Aber diese Informationen allein ist oft nicht ausreichend und die Fragen, warum der tägliche Datenimport nicht erfolgreich war oder ob das Gefühl, dass ein Auftrag immer länger dauert, richtig ist, kann damit nicht beantwortet werden.
Wie wäre es, wenn man die im Blogbeitrag gezeigten Informationen zur Auftragsausführung in einer DeltaMaster-Analysesitzung zur Verfügung stellen würde? So könnte beispielsweise die Zeit der Auftragsausführung oder auch der einzelnen Auftragsschritte in der Historie betrachtet und schnell erkannt werden, ob die Auftragsausführung tatsächlich langsamer wird. Dies soll Thema eines weiteren Beitrags sein.