Tabellen
Die MSDB-Datenbank besteht aus vielen Objekten, und die Möglichkeit, einige dieser Tabellen direkt abzufragen, kann sehr hilfreich sein. Wir werden uns einige der Tabellen ansehen, die für eine Abfrage nützlich sind.
In diesem Tutorial werden einige Gruppen von MSDB-Tabellen vorgestellt, die häufig abgefragt werden.
SELECT * FROM dbo.suspect_pages;Suspect_pages hilft beim Aufspüren beschädigter Datenseiten oder solcher, die aufgrund eines ausgefallenen Plattensubsystems beschädigt werden könnten. Dabei kann es sich um Seiten handeln, die bei einer normalen Abfrage mit einem 823- oder 824-Fehler gefunden wurden, die durch einen DBCC-Check-Befehl gefunden wurden oder die bei einer Prüfsummenüberprüfung einer Sicherung gefunden wurden. DBAs sollten sich dieser Tabelle bewusst sein und sie gelegentlich abfragen, um sicherzustellen, dass keine unkorrigierten Fehler vorhanden sind.
Für diese Tabelle gibt es eine Grenze von 1000 Zeilen. Hoffentlich ist das keine Zahl, die häufig getestet wird, aber falls doch, werden ältere Zeilen nicht automatisch gelöscht, um Platz für neue zu schaffen. SQL Server hört einfach auf, Informationen aufzuzeichnen, wenn 1000 Zeilen in der Tabelle vorhanden sind. Aus diesem Grund sollten ältere Zeilen von einem DBA gelöscht werden, nachdem er die Ursache für diese Zeilen überprüft und geklärt hat.
Suspect_pages hilft beim Aufspüren beschädigter Datenseiten oder solcher, die aufgrund eines ausgefallenen Plattensubsystems beschädigt werden könnten. Dabei kann es sich um Seiten handeln, die bei einer normalen Abfrage mit einem 823- oder 824-Fehler gefunden wurden, die durch einen DBCC-Check-Befehl gefunden wurden oder die bei einer Prüfsummenüberprüfung einer Sicherung gefunden wurden. DBAs sollten sich dieser Tabelle bewusst sein und sie gelegentlich abfragen, um sicherzustellen, dass keine unkorrigierten Fehler vorhanden sind.
Für diese Tabelle gibt es eine Grenze von 1000 Zeilen. Hoffentlich ist das keine Zahl, die häufig getestet wird, aber falls doch, werden ältere Zeilen nicht automatisch gelöscht, um Platz für neue zu schaffen. SQL Server hört einfach auf, Informationen aufzuzeichnen, wenn 1000 Zeilen in der Tabelle vorhanden sind. Aus diesem Grund sollten ältere Zeilen von einem DBA gelöscht werden, nachdem er die Ursache für diese Zeilen überprüft und geklärt hat.
SELECT backup_set_id, database_name, bset.media_set_id, position, family_sequence_number, physical_device_name
FROM msdb.dbo.backupset bset
INNER JOIN backupmediafamily bfam ON bset.media_set_id = bfam.media_set_id;Die Tabelle backupset enthält jedes Mal eine Zeile, wenn eine Sicherung erstellt wird. Die Tabelle backupmediafamily enthält eine Zeile für jede erstellte oder angehängte Sicherungsdatei. Die meisten DBAs sichern eine Datenbank pro Datei und belassen diese Tabellen in einer 1:1-Beziehung. Wenn Sie Striped Backups verwenden oder mehrere Backups in eine einzige Datei packen, sind sie nicht 1:1.
Betrachten Sie diese Ergebnismenge der obigen Abfrage aus einer Striped-Sicherung.
Es gibt 2 Zeilen, aber nur eine einzige Backup-Set-ID, Nummer 1. Das bedeutet, dass es nur eine Sicherung gibt. Die Sicherung wurde an dieselbe Mediensatz-ID, Nummer 1, gesendet, aber dieser Satz enthält 2 Sequenznummern. Dies bedeutet, dass für eine Wiederherstellung der Datenbank beide Dateien benötigt werden und aufgelistet werden müssen.
RESTORE HEADERONLY FROM
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Stripe1.bak',
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Stripe2.bak';Betrachten Sie nun diese Ergebnismenge für eine Reihe von Backups, die sich eine einzige Datei teilen.
Es gibt zwei verschiedene Sicherungssätze, 2 und 3, aber einen einzigen Mediensatz und einen einzigen Dateinamen. Das bedeutet, dass sich mehrere Sicherungen eine einzige Datei teilen. Beide Zeilen haben eine Sequenz von 1, was bedeutet, dass es für keine der beiden Sicherungen ein Striping gibt. In diesem Fall wird die Positionsspalte wichtig. "DATEI" 1 ist eine weitere Testsicherung, während “DATEI" 2 eine MSDB-Sicherung ist.
RESTORE HEADERONLY FROM
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\MultiBackupFile.bak'
WITHH FILE = 1;
RESTORE HEADERONLY FROM
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\MultiBackupFile.bak'
WITH FILE = 2;In dieser Tabelle werden alle dbmail-bezogenen Ereignisse aufgezeichnet. Es handelt sich um dieselben Informationen, die in SSMS über die Kontextmenüoption View Database Mail Log abgerufen werden können.
SELECT sp.profile_id, sp.name, spa.account_id, sa.name, sa.email_address, sa.display_name, ISNULL(spp.is_default, 0) IsDefaultProfile
FROM [dbo].[sysmail_profile] sp
INNER JOIN [dbo].[sysmail_profileaccount] spa ON sp.profile_id = spa.profile_id
INNER JOIN [dbo].[sysmail_account] sa ON spa.account_id = sa.account_id
LEFT OUTER JOIN [dbo].[sysmail_principalprofile] spp ON sp.profile_id = spp.profile_id;In dieser Tabellengruppe werden die Einstellungen der DBMail-Profile und -Konten gespeichert. Die Tabelle sysmail_profile enthält eine Zeile pro Profil, während sysmail_account eine Zeile für jedes Konto enthält. Jede Zeile in der Tabelle sysmail_profileaccount entspricht einem Profil und einem Konto. Dies ermöglicht eine Many-to-many-Beziehung zwischen Profilen und Konten.
Dieses Beispiel zeigt 2 Profile, die sich ein einziges Konto teilen.
Kann ich den Code für die Systemtabellen und -ansichten in MSDB sehen und wenn ja, wie?
Die Systemtabellen in MSDB können mit sp_help oder über das Kontextmenü "Design" im Object Explorer angezeigt werden.
exec sp_help sysjobs;
Die Ansichten haben im Gegensatz zu den Benutzeransichten keine Option Ändern oder Skripten als in ihrem Kontextmenü, aber sie können trotzdem mit sp_helptext angezeigt werden.
exec sp_helptext sysjobs_view;
Kann ich diese Objekte in der MSDB-Datenbank ändern?
SQL Server erlaubt es, die Systemtabellen und -ansichten zu ändern, aber das sollte auf keinen Fall geschehen. Die Assistenten sowie verschiedene Ansichten in SSMS und Azure Data Studio erwarten, dass sich diese Objekte auf eine bestimmte Art und Weise verhalten, und eine Änderung könnte dazu führen, dass diese Funktionen fehlschlagen.
Eine Ausnahme kann für nicht geclusterte Indizes gemacht werden. Auf einer Instanz mit vielen Datenbanken und einem großen Backup-Verlauf oder vielen SQL Server Agent-Aufträgen mit einem großen Verlauf können einige Überwachungsabfragen untragbar langsam laufen, und das Hinzufügen von nicht geclusterten Indizes zur Unterstützung von Berichten oder anderen Vorgängen kann dieses Problem lösen.
Kann ich meine eigenen Objekte in der MSDB-Datenbank speichern?
SQL Server erlaubt das Hinzufügen von Objekten zur MSDB-Datenbank, aber das sollte wahrscheinlich nicht erlaubt werden. Legen Sie stattdessen eine spezielle Benutzerdatenbank mit einem Namen wie "_DBA" an, in der Administratoren Objekte speichern können, die zur Verwaltung der Instanz verwendet werden.
Datenbank Objekte
Zusätzlich zu den Tabellen, in denen die Daten gespeichert sind, enthält MSDB auch mehrere gespeicherte Prozeduren, die Sie direkt ausführen können.
Welches sind die wichtigsten gespeicherten Prozeduren in der MSDB-Datenbank, die SQL Server-Profis kennen sollten?
Auch hier könnte ein ganzes Buch diesen Prozeduren gewidmet werden. Hier ist eine Auswahl der gängigsten Prozeduren.
exec sp_help_jobactivityDie Prozedur sp_help_jobactivity liefert eine Momentaufnahme aller SQL Server Agent Jobs einschließlich ihres aktuellen Ausführungsstatus und der Details der letzten Ausführung. Sie ist sehr nützlich bei der Überwachung von Jobs, die eigentlich laufen sollten, es aber nicht tun. Solche Aufträge haben einen gültigen start_execution_date-Wert, aber einen NULL-Wert für stop_execution_date.
exec sp_start_job
exec sp_stop_jobDiese beiden Prozeduren starten bzw. stoppen einen SQL Server Agent Job. Sie akzeptieren entweder einen Jobnamen oder eine Job-ID als Parameter. Der Startbefehl akzeptiert optional auch eine Schritt-ID, wenn der Job an einer anderen Stelle als üblich gestartet werden soll.
Diese Prozeduren können nützlich sein, wenn Sie versuchen, bestimmte Aufgaben zu automatisieren oder nach Fehlern automatisch wiederherzustellen.
exec sp_send_dbmail
exec sp_notify_operatorDieses Prozedurenpaar kann zum Versenden von E-Mail-Nachrichten von einem SQL Server aus verwendet werden, wenn DBMail eingerichtet wurde. sp_send_dbmail ist in der Regel die beliebtere Prozedur, da sie alle üblichen E-Mail-bezogenen Optionen wie to, cc, bcc, subject, body und attachments - neben anderen - akzeptiert. Einer der beliebtesten optionalen Parameter ist die Möglichkeit, die Ausgabe einer Abfrage in die Nachricht aufzunehmen.
sp_nofity_operator hat weit weniger Optionen, akzeptiert aber entweder einen Operator-Namen oder eine ID anstelle einer E-Mail-Adresse. Diese Abstraktionsebene kann für einige Administratoren nützlich sein, da die zugrunde liegende E-Mail-Adresse des Operators geändert werden kann, wenn sich die Personalgruppierungen ändern, anstatt den Code zu ändern, um eine neue E-Mail-Adresse zu berücksichtigen. Eine ähnliche Funktionalität kann erreicht werden, wenn beim Schreiben von Code, der sp_send_dbmail verwendet, immer Mailinglisten verwendet werden.
Kann ich den Code für die MSDB-Systemobjekte sehen und wenn ja, wie?
Die Systemprozeduren können im Objekt-Explorer unter "Programmierbarkeit", "Gespeicherte Prozeduren" und schließlich "Gespeicherte Systemprozeduren" eingesehen werden. Über die Option "Ändern" eines Kontextmenüs können Sie die Objektdefinition aufrufen.
Es ist sinnvoll, einige dieser Objekte zu überprüfen, da man auf diese Weise mehr über das Schema von MSDB erfährt, um bessere Abfragen für die Objekte zu schreiben.
Kann ich diese Objekte in der MSDB-Datenbank ändern?
Genau wie die Tabellen und Ansichten können auch die Systemprozeduren von SQL Server geändert werden. Aus denselben Gründen wie die Tabellen und Ansichten sollten auch die Prozeduren nicht geändert werden.
Berechtigungen
In diesem Abschnitt werden die erforderlichen Berechtigungen für den Zugriff auf die Daten und Objekte in der MSDB-Datenbank behandelt.
Wer hat Zugriff auf die MSDB-Datenbank und gibt es verschiedene Berechtigungsstufen?
MSDB ist für jeden Benutzer mit öffentlichem Zugriff auf die Instanz verfügbar. Die Liste der verfügbaren Tabellen ist jedoch begrenzt und schreibgeschützt.
Die gesamte Liste der Tabellen wird mit der Mitgliedschaft in der festen Rolle db_datareader, db_owner oder sysadmin verfügbar gemacht. Schreibrechte für diese Tabellen werden mit db_datawriter, db_owner oder der festen Rolle sysadmin gewährt. Beachten Sie, dass nur die sysadmin-Mitgliedschaft einem Benutzer Zugriff auf die grafische Benutzeroberfläche des SQL Server-Agenten für Aufträge gibt, deren Eigentümer er nicht ist, dass er aber in der Lage ist, die zugrunde liegenden Tabellen mit diesen Berechtigungen auf Datenbankebene direkt zu ändern.
Datenbank Backup
Eine häufig gestellte Frage im Zusammenhang mit MSDB-Datenbanken ist, ob und wie man die Datenbank sichern muss.
Muss ich die MSDB-Datenbank sichern?
Ein klares Ja! Für den Fall, dass die MSDB-Datenbank beschädigt wird oder der Server abstürzt und die MSDB-Datenbank wiederhergestellt werden muss, ist ein Backup die einzige Möglichkeit, dies zu tun. Diese Datenbank enthält auch Daten, die versehentlich von einem administrativen Benutzer beschädigt werden könnten. Wenn eine solche Person einen SQL Server Agent Job, Step oder Operator ändert oder löscht, kann ein Objekt über ein MSDB-Backup wiederhergestellt werden.
Es wird allgemein als beste Praxis angesehen, jeden Tag ein Backup der gesamten MSDB-Datenbank zu erstellen. Falls die MSDB auf einer Instanz außergewöhnlich groß ist, können Differentiale verwendet werden.
Wie kann ich die MSDB-Datenbank sichern?
Die MSDB-Datenbank kann auf die gleiche Weise gesichert werden wie jede andere Datenbank. Der unten stehende Code sichert die Datenbank in eine lokale Datei. Ziehen Sie in Erwägung, einen Speicherort zu verwenden, der nicht lokal auf dem SQL Server liegt - wenn dies möglich ist -, da dies das Risiko eines Verlusts der MSDB-Datenbank im Falle eines Serverausfalls verringert. Es empfiehlt sich, täglich ein Backup zu erstellen. Die Größe der Datei lässt dies im Allgemeinen zu.
BACKUP DATABASE MSDB TO DISK='C:\MSDB.bak';Welches Wiederherstellungsmodell sollte die MSDB-Datenbank haben und kann das Wiederherstellungsmodell geändert werden?
Für die MSDB-Datenbank ist standardmäßig ein einfaches Wiederherstellungsmodell ausgewählt. Dies ist in der Regel akzeptabel, aber je nach den Wiederherstellungsanforderungen für die Instanz muss es möglicherweise in ein vollständiges Modell geändert werden. Überlegen Sie, ob sich Dinge wie die Auftragsschritte des SQL Server-Agenten so häufig ändern, dass eine zeitpunktbezogene Wiederherstellung erforderlich ist. Stellen Sie sicher, dass bei einer Änderung des Wiederherstellungsmodells die Protokollsicherungen in regelmäßigen Abständen geplant werden.
Wiederherstellung
Ist eine Wiederherstellung der MSDB-Datenbank überhaupt erforderlich?
Die MSDB-Datenbank muss wiederhergestellt werden, wenn sie bei einer Konsistenzprüfung beschädigt wurde. Eine Wiederherstellung kann auch erforderlich sein, wenn ein Objekt, z. B. ein Agent-Job-Step, unsachgemäß geändert wurde.
Wie kann die MSDB-Datenbank wiederhergestellt werden?
Der erste Schritt bei der Wiederherstellung von MSDB besteht darin, zu entscheiden, ob die gesamte Datenbank wiederhergestellt werden muss oder ob nur ein Objekt oder eine Zeile wiederhergestellt werden muss.
Wenn es sich um eine Beschädigung handelt, ist die Wiederherstellung der gesamten Datenbank wahrscheinlich die beste Methode. Wenn eine Wiederherstellung in Betracht gezogen wird, weil jemand einen SQL Server Agent-Auftrag geändert oder einen Operator gelöscht hat, ist eine Wiederherstellung auf Objektebene sinnvoller.
Um eine Wiederherstellung auf Objektebene durchzuführen, stellen Sie einfach eine aktuelle MSDB-Sicherung in einer neuen Benutzerdatenbank mit einem Namen wie MSDB_Recover wieder her. Anschließend werden die erforderlichen Tabellen abgefragt, um die fehlenden Daten anzuzeigen und diese Informationen zu verwenden, um das defekte Objekt wiederherzustellen. Eine Gruppe allgemeiner Tabellen, die abgefragt werden können, finden Sie weiter oben in diesem Lernprogramm.
Anweisungen für eine vollständige Wiederherstellung der Datenbank finden Sie weiter oben in diesem Artikel im Abschnitt über das Verschieben von MSDB.
