In dieser Tutorial-Reihe werden wir häufige Fragen im Zusammenhang mit der SQL Server msdb-Datenbank behandeln. Dies ist eine der Standard-Systemdatenbanken, die Teil jeder SQL Server-Installation sind. In diesem Tutorial erfahren Sie, wofür die msdb-Datenbank verwendet wird, wie sie konfiguriert und gewartet wird und welche Daten und Objekte in der msdb-Datenbank gespeichert sind.
Die folgenden Abschnitte werden in diesem Lernprogramm behandelt:
- SQL Server MSDB Database Übersicht
- SQL Server MSDB Database Lokation
- SQL Server MSDB Database Größe und Wachstum
- SQL Server MSDB Database Wartung
- SQL Server MSDB Database Tabellen
- SQL Server MSDB Database Objekte
- SQL Server MSDB Database Berechtigungen
- SQL Server MSDB Database Backup
- SQL Server MSDB Database Wiederherstellungs-Modell
- SQL Server MSDB Database Wiederherstellung
- SQL Server MSDB Database Notfallplan
Lassen Sie uns loslegen!
Überblick
In diesem Abschnitt behandeln wir Fragen zum Zweck der MSDB-Datenbank, ob Sie einen SQL Server ohne eine MSDB-Datenbank ausführen können und vieles mehr.
Was ist die MSDB-Datenbank und was ist ihr Zweck?
Die MSDB-Datenbank ist eine von 4 sichtbaren Systemdatenbanken - Master, Model und TempDB bilden dabei die übrigen 3. Ihr Zweck ist es, die Historie einer Reihe von üblichen DBA-Aktivitäten wie Backups und Wiederherstellungen zu verfolgen. Sie enthält auch alle Daten für den SQL Server Agent, einschließlich der Jobs, Schritte, Operatoren, Warnungen und der Ausführungshistorie. Manchmal wird MSDB zum Speichern von SSIS-Paketen verwendet, obwohl diese auf moderneren Instanzen eher in einer SSIS-Katalogdatenbank gespeichert werden.
Wenn Sie den Befehl sp_helpdb ausführen, können Sie sehen, dass MSDB die Datenbank-ID Nummer 4 ist.
Kann ich SQL Server ohne eine MSDB-Datenbank betreiben?
Ein SQL Server kann für einen kurzen Zeitraum ohne eine Online-MSDB-Datenbank laufen, da die meisten gängigen SQL Server-Aktivitäten wie die Abfrage einer Benutzerdatenbank keine MSDB erfordern. Dies kann während der Wiederherstellung der MSDB auftreten oder wenn die Datenbank aufgrund einer Beschädigung nicht geladen werden kann.
Die in der folgenden Abbildung gezeigte Instanz von SQL Server wurde gestartet, nachdem die Daten- und Protokolldateien für MSDB absichtlich in falsche Werte umbenannt worden waren, so dass SQL Server sie nicht finden konnte. Alle anderen Datenbanken sind ordnungsgemäß online gegangen.
In diesem Fall sind die meisten gängigen Datenbankabfragen wie SELECT- und INSERT-Anweisungen, die an andere Datenbanken gerichtet sind, erfolgreich. Operationen, die von MSDB abhängen, zeigen ein seltsames Verhalten.
Wenn Sie beispielsweise versuchen, eine Datenbank zu sichern, wird eine Datei im Ziel gespeichert, aber die Anweisung schlägt fehl und zeigt an, dass diese Informationen nicht in MSDB aufgezeichnet werden konnten.
Processed 360 pages for database 'sampleDB', file 'sampleDB' on file 1.
100 percent processed.
Processed 2 pages for database 'sampleDB', file 'sampleDB_log' on file 1.
Msg 945, Level 14, State 2, Line 1
Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 3009, Level 16, State 1, Line 1
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
BACKUP DATABASE successfully processed 362 pages in 0.305 seconds (9.248 MB/sec).In diesem Szenario ist der Versuch, den SQL Server-Agenten zu starten, erfolgreich - auch wenn die MSDB nicht online ist! Der Versuch, die Liste der Agentenaufträge in SSMS anzuzeigen, führt jedoch zu einem Fehler.
Alternativ können Sie SQL Server Trace Flag 3608 verwenden, um SQL Server anzuweisen, nur die Master-Datenbank und keine anderen System- oder Benutzerdatenbanken zu starten. Dieses Flag wird in der Regel nur in extremen Wiederherstellungsszenarien verwendet, würde aber technisch gesehen die Ausführung eines SQL-Servers ohne MSDB ermöglichen.
Ist die Version der MSDB-Datenbank mit der Version, der Edition und dem Patch-Level von SQL Server identisch?
Im Laufe der Jahre hat MSDB zwischen den Hauptversionen von SQL Server einige kleine Überarbeitungen durchlaufen. In SQL Server 2008 wurde beispielsweise die Spalte "compressed_backup_size" hinzugefügt, um die Größe einer Datenbanksicherungsdatei aufzuzeichnen, wenn sie komprimiert wurde, da dies eine neue Funktion für diese Version war. Die für die Speicherung von SSIS-Paketen benötigten Objekte wurden 2005 hinzugefügt, und die Objekte zur Speicherung von DTS-Paketen wurden später weggelassen, als die Funktion entfernt wurde.
Die Edition und der Patch-Level haben keinen Einfluss auf MSDB, selbst wenn es sich um die Express-Edition handelt, die einige für MSDB erforderliche Funktionen wie den SQL Server Agent nicht enthält.
Können Sie die MSDB-Datenbank löschen und sollten Sie dies aus irgendeinem Grund tun?
SQL Server kann nicht über einen längeren Zeitraum ohne eine MSDB-Datenbank ausgeführt werden. Sie kann also nicht gelöscht, offline genommen oder abgetrennt werden. Außerdem kann sich der Autor keinen Grund vorstellen, warum man die MSDB-Datenbank löschen sollte, es sei denn, man möchte sie sofort wiederherstellen, was mit einer Wiederherstellung unter Verwendung der Option REPLACE möglich wäre.
DROP DATABASE msdb;
ALTER DATABASE msdb SET OFFLINE;
EXEC master.dbo.sp_detach_db @dbname = N'msdb';
Kann man die MSDB-Datenbank umbenennen und sollte man dies aus irgendeinem Grund tun?
Die MSDB-Datenbank kann nicht umbenannt werden. SQL Server und SQL Server Agent erwarten, dass die Datenbank online und unter dem Namen MSDB verfügbar ist. Auch hier kann sich der Autor keinen Grund vorstellen, warum man die MSDB-Datenbank umbenennen sollte.
exec sp_renamedb 'msdb', 'anythingElse';
Datenbank Location
Im folgenden Abschnitt dieser Reihe befassen wir uns mit dem Speicherort der Dateien, das Verschieben der MSDB-Datenbankdateien und die Dateien, aus denen die MSDB-Datenbank besteht.
Welche physischen Dateien, Namen, Speicherorte usw. unterstützen die MSDB-Datenbank?
Die MSDB-Datenbank besteht aus 2 Dateien - einer Datendatei und einer Protokolldatei. Die Datendatei hat den logischen Namen "MSDBData" und den Standarddateinamen "MSDBData.mdf". Die Protokolldatei heißt "MSDBLog" bzw. "MSDBLog.ldf".
Dies ist die Mindestanzahl von Dateien für jede Datenbank, und MSDB bildet hier keine Ausnahme. SQL Server erlaubt das Hinzufügen weiterer Dateien zu MSDB, aber es ist schwer vorstellbar, dass eine MSDB-Datenbank so groß wird, dass mehrere Dateien erforderlich sind.
Mit SQL Server können Sie entweder die physischen oder die logischen Namen dieser beiden Dateien umbenennen, aber es gibt wirklich keinen Grund, eine solche Operation durchzuführen. Es hat keinen Zweck und würde die Umgebung unnötig kompliziert machen.
Wo finde ich die MSDB-Datenbank auf der Festplatte und in SSMS?
Es gibt mehrere Möglichkeiten, die Dateien in SSMS zu finden. Sie können aus dem DMO sysfiles gelesen werden.
select name, filename from MSDB.sys.sysfiles;Die Dateien werden auch im Fenster mit den Datenbankeigenschaften aufgelistet, wie im vorherigen Screenshot zu sehen.
Beim Durchsuchen der Festplatte sehen die Dateien wie jeder andere Satz von Datenbankdateien aus.
Macht es einen Unterschied, wo sich die MSDB-Datenbankdateien befinden (müssen sie auf C:, SAN usw. liegen)?
Die MSDB-Datenbankdateien sollten zusammen mit den anderen Datenbankdateien desselben Typs gespeichert werden. Das bedeutet im Allgemeinen, dass sie NICHT auf dem Laufwerk C: gespeichert werden. Die Screenshots in diesem Beitrag zeigen nur das Laufwerk C:, da sie von einer lokalen Instanz auf dem Laptop des Autors stammen, die nur ein Laufwerk C: enthält.
Seit SQL Server 2012 bietet das Installationsprogramm die Möglichkeit, auszuwählen, wo die Systemdatenbankdateien gespeichert werden sollen. Davor wurden sie zusammen mit den SQL Server-Binärdateien installiert und mussten nach der Installation an den Zielspeicherort verschoben werden.
Kann die MSDB-Datenbank verschoben werden?
Ja! Die MSDB-Datenbankdateien können verschoben werden. Es gibt 2 Methoden zum Verschieben der MSDB-Datenbankdateien. Die eine ist eine gängige Methode, insbesondere bei einer Neuinstallation mit begrenzten Benutzern oder Betriebszeitanforderungen. Die andere lässt die SQL Server-Instanz weiterlaufen und unterbricht nur die MSDB-abhängigen Teile des Betriebs wie den SQL Server Agent.
Möglichkeit 1
Dies ist die häufigere Methode, die im Allgemeinen durch das Ergebnis einer Google-Suche vorgeschlagen wird.
Schritt 1 - Informieren Sie SQL Server, dass sich die MSDB-Datenbankdaten und/oder die Protokolldatei(en) beim nächsten Start des Dienstes an einem anderen Ort befinden werden.
Während dieses Schritts kann der Dateiname geändert werden, aber es wird nicht empfohlen, den physischen Dateinamen oder die Erweiterung zu ändern.
Der folgende Code ändert den erwarteten Speicherort der MSDB-Datenbankdaten bzw. der Protokolldateien von dem Ort, an dem sie sich derzeit befinden, auf das Stammlaufwerk C. Er geht von den logischen Standardnamen aus.
ALTER DATABASE [MSDB] MODIFY FILE ( NAME = MSDBData , FILENAME = 'C:\MSDBData.mdf' );
ALTER DATABASE [MSDB] MODIFY FILE ( NAME = MSDBLog, FILENAME = 'C:\MSDBLog.ldf' );Wenn Sie diese Befehle ausführen, erhalten Sie eine eindeutige Ausgabemeldung.
Die Datei "MSDBData" wurde im Systemkatalog geändert. Der neue Pfad wird beim nächsten Start der Datenbank verwendet.
Die Datei "MSDBLog" wurde im Systemkatalog geändert. Der neue Pfad wird beim nächsten Start der Datenbank verwendet.
Schritt 2 - Nachdem SQL Server über die neue(n) Position(en) informiert wurde, stoppen Sie die Dienste SQL Server und SQL Server Agent, damit die Sperre für die 2 (oder mehr) MSDB-Datenbankdateien aufgehoben wird.
Schritt 3 - Verschieben Sie die Datei(en) an den/die vorgesehenen Speicherort(e), der/die dem SQL Server in Schritt 1 mitgeteilt wurde(n).
Schritt 4 -Starten Sie den SQL Server-Dienst.
SQL Server sollte sofort starten. Überprüfen Sie sofort die MSDB in SSMS. Sieht sie so aus wie auf dem Screenshot ganz am Anfang dieses Tutorials mit den Worten "(Recovery Pending)" am Ende? Wenn ja, gibt es ein Problem mit dem Dateispeicherort der MSDB-Dateien. Vielleicht befinden sie sich nicht an dem Ort, an dem der Katalog sie vermutet, oder sie befinden sich am richtigen Ort, aber das Dienstkonto kann sie aufgrund eines Berechtigungsproblems nicht sehen. Wenn Sie Windows verwenden, bietet die Ereignisanzeige manchmal weitere Details darüber, warum einige Datenbanken nicht online gehen, und sollte konsultiert werden. Kümmern Sie sich auf jeden Fall darum, das Problem zu beheben, und überlegen Sie, ob Sie die Dateien gegebenenfalls zurücklegen, um die Instanz wieder zum Laufen zu bringen.
Möglichkeit 2
Stoppen Sie zunächst den SQL Server Agent-Dienst und beenden Sie alle anderen Prozesse, die MSDB verwenden könnten. Sichern Sie dann einfach die MSDB-Datenbank und stellen Sie MSDB sofort mit der Option REPLACE wieder her und verwenden Sie das Schlüsselwort MOVE, um einen neuen Speicherort für alle Dateien auszuwählen.
BACKUP DATABASE [msdb] TO DISK = N'C:\backups\MSDB.bak';
GO
RESTORE DATABASE [msdb] FROM DISK = N'C:\backups\MSDB.bak'
WITH REPLACE,
MOVE N'MSDBData' TO N'C:\Backups\MSDBData_NEW.mdf',
MOVE N'MSDBLog' TO N'C:\Backups\MSDBLog_NEW.ldf';
GOWenn die Wiederherstellung erfolgreich abgeschlossen ist, starten Sie den SQL Server Agent neu.
Größe und Wachstum
Genau wie jede andere SQL Server-Datenbank kann auch die MSDB-Datenbank wachsen. In diesem Abschnitt befassen wir uns mit dem Wachstum und der typischen Größe der MSDB-Datenbank.
Wie sollten die Wachstumseinstellungen der Datenbank und des Transaktionsprotokolls für die MSDB-Datenbank konfiguriert werden?
Das Wachstum der Daten- und Protokolldateien der MSDB-Datenbank sollte denselben Regeln folgen, die auch für die meisten Benutzerdatenbanken gelten. MSDB verhält sich viel mehr wie eine Benutzerdatenbank mit geringem Volumen als die anderen Systemdatenbanken. Eine Dateigröße von 64 MB ist ein guter Ausgangspunkt. Versuchen Sie, die Größe der Protokolldatei so zu wählen, dass sie nicht wachsen muss. Die richtige Größe für eine Instanz hängt von vielen Faktoren ab, wie in den nächsten Abschnitten beschrieben.
Wie groß ist die MSDB-Datenbank normalerweise?
Das kann sehr unterschiedlich sein - so sehr, dass es eigentlich keine typische Größe gibt. Es ist nicht ungewöhnlich, dass MSDB 50 MB oder 3000 MB groß ist. MSDB enthält einige Tabellen, die in bestimmten Szenarien sehr groß werden können, während die Größe in anderen Fällen vernachlässigbar sein kann.
Wie groß kann die MSDB-Datenbank werden?
MSDB speichert die Sicherungs- und Wiederherstellungshistorie. Stellen Sie sich einen Server mit 2000 Datenbanken vor, der alle 15 Minuten ein Log-Backup von allen Datenbanken erstellt. Das sind 192.000 neue Zeilen in den Tabellen backupset und backupmediaset tagtäglich! Wenn man einige Wochen oder Monate der Historie aufbewahrt, kann die Größe der MSDB astronomisch ansteigen.
Wenn die Backups in derselben Datenbank von einem SQL Server Agent-Job aufgerufen werden, bedeutet das 96 Job-Historienzeilen nur für diesen einen Job. Speichert dieser Auftrag die Ausgabe jedes Schritts und hat keine Größenbeschränkung? Vielleicht speichert diese Instanz auch eine große Reihe von SSIS-Paketen in der MSDB. Das kann noch mehr Platz beanspruchen und die Größe von MSDB auf mehrere Gigabyte erhöhen.Ein anderer Server hat vielleicht keine SSIS-Pakete, nur 5 Benutzerdatenbanken und führt stündliche Log-Backups nur für 2 davon durch. In diesem Fall wäre die MSDB um mehrere Größenordnungen kleiner.
Datenbank Wartung
In diesem Teil geht es um die Art der Wartungsroutinen, die Sie für die MSDB-Datenbank durchführen sollten.
Können Sie die Datenbank und das Transaktionsprotokoll für die MSDB-Datenbank verkleinern?
Die Größe der Daten- und Protokolldateien für die MSDB-Datenbank kann geändert werden. Wie bei jeder anderen Datenbank auch, ist dies ein Vorgang, der nicht sehr häufig erforderlich sein sollte. Wenn Sie nach der Lektüre dieses Tutorials eine aggressivere Haushaltsroutine einführen, um einige der Verlaufstabellen zu löschen, dann ist vielleicht eine einmalige Verkleinerung gerechtfertigt.
Der folgende Code würde die Daten- und Protokolldateien der MSDB-Datenbank auf 256 bzw. 64 Megabyte verkleinern. Er geht von den Standardnamen der logischen Dateien aus. Dieser Code würde im Kontext von MSDB ausgeführt werden.
DBCC SHRINKFILE (N'MSDBData' , 256);
DBCC SHRINKFILE (N'MSDBLog' , 64);Sollten Sie MSDB-Indizes neu erstellen und wie lautet der Code?
Die Daten in MSDB sind manchmal groß und ändern sich häufig, da neue Zeilen in Protokolltabellen eingefügt werden, während ältere Zeilen auslaufen. Daher ist es ratsam, eine traditionelle Indexwartung für MSDB durchzuführen. Wenn es nicht bereits einen anderen Wartungsjob gibt, der MSDB mit einbezieht, könnte ein Wartungsplan angebracht sein. Dieser Screenshot zeigt einen, der mit dem Assistenten und allen Standardwerten nur für MSDB erstellt wurde.
Sollten Sie Integritätsprüfungen für die MSDB-Datenbank durchführen und wie lautet der Code?
Auf jeden Fall, ja! Sie sollte jeden Tag durchgeführt werden. Viele SQL Server-Aufgaben hängen vom SQL Server-Agent ab, und der SQL Server-Agent hängt von MSDB ab. Ohne MSDB wird jeder Sicherungsvorgang mit einem Fehler enden. Für einen SQL Server-DBA ist es von größter Wichtigkeit, MSDB-Beschädigungen so schnell wie möglich zu finden und zu beheben.
Der Code für die Integritätsprüfung der MSDB ist unten aufgeführt. Alternativ könnte ein Schritt hinzugefügt werden, um diesen Vorgang abzuschließen, wenn ein Wartungsplan verwendet wird, wie im vorherigen Schritt gezeigt.
DBCC CHECKDB ('MSDB');