SQLShack

In diesem Artikel werden die Verwendung mehrerer SQL Server-Transaktionsprotokolldateien und der Vorgang zum Entfernen der sekundären Transaktionsprotokolldatei erläutert.

Einleitung

Standardmäßig enthält eine SQL Server-Datenbank eine Primärdatendatei und eine Transaktionslogdatei. Es empfiehlt sich, mehrere Datendateien zu konfigurieren und Daten auf mehrere Datendateien aufzuteilen. Wir können diese Datendateien in separaten Speicherlaufwerken haben, um mehrere E / A zu haben. Es hilft bei der Datenverwaltung, verbessert die Leistung, plant Backup-Ansätze nach Dateigruppen.

Wir können auch mehrere Transaktionsprotokolldateien für eine Datenbank haben.

SQL Server verwendet die mehreren Transaktionsprotokolldateien in sequentieller Reihenfolge. Angenommen, wir haben zwei Transaktionsprotokolldateien. SQL Server verwendet jeweils eine Protokolldatei, und sobald sie voll ist, verwendet SQL Server eine andere Protokolldatei. Da SQL Server es nicht parallel verwendet, erhalten wir keinen Leistungsvorteil davon. Idealerweise sollten wir nur eine Transaktionsprotokolldatei pro Datenbank haben.

Nehmen wir an, wir haben eine Produktionsdatenbank und erhalten eine kritische Warnung, dass der Speicherplatz auf der Festplatte knapp wird. Nach einer Untersuchung haben wir herausgefunden, dass dieses Laufwerk eine Transaktionsprotokolldatei enthält. Aufgrund aktiver Transaktionen ist diese SQL Server-Transaktionsprotokolldatei voll. Wir haben versucht, die Protokolldatei zu verkleinern, aber es hat nicht funktioniert. Wir sichern auch das Transaktionsprotokoll, aber aufgrund aktiver Transaktionen konnte der benötigte Speicherplatz nicht freigegeben werden. Durch Hinzufügen einer weiteren Transaktionsprotokolldatei auf einer separaten Festplatte mit freiem Speicherplatz wird dieses Problem behoben.

Da SQL Server den seriellen Modus zum Schreiben von Daten in eine Transaktionsprotokolldatei verwendet, sollten wir die zusätzliche Protokolldatei später entfernen. Lassen Sie uns den Prozess des Entfernens einer zusätzlichen Protokolldatei untersuchen.

Erstellen einer Datenbank mit mehreren SQL Server-Transaktionsprotokolldateien

Herstellen einer Verbindung zu einer SQL-Instanz in SQL Server Management Studio. Klicken Sie im Objekt-Explorer mit der rechten Maustaste auf den Knoten Datenbanken und klicken Sie auf den Befehl Neue Datenbank:

Es öffnet sich das Fenster Neue Datenbank. Geben Sie einen Datenbanknamen an und fügen Sie eine weitere Transaktionsprotokolldatei hinzu. Zu Testzwecken haben wir das automatische Wachstum von Transaktionsprotokolldateien deaktiviert. Die anfängliche Größe der Transaktionsprotokolldatei beträgt 8 MB:

Nachdem alles festgelegt wurde, klicken Sie auf die Schaltfläche OK, um eine Datenbank in einem Standardverzeichnis für Daten / Protokolldateien zu erstellen. Erstellen einer Tabelle und Einfügen von Daten in:

Anzeigen des Status der virtuellen Protokolldatei in SQL Server-Transaktionsprotokolldateien

Im Artikel SQL Server-Transaktionsprotokollarchitektur haben wir den Status der Transaktionsprotokolldatei untersucht. Jede Transaktionsprotokolldatei besteht aus mehreren virtuellen Protokolldateien. Eine Transaktionsprotokolldatei ist eine Kombination mehrerer virtueller Protokolldateien (VLF). Der folgende Screenshot zeigt die physische und logische Architektur der Protokolldatei:

SQL Server erstellt eine Datenbank mit minimalem VLF basierend auf der anfänglichen Protokollgröße und der Auto-Grow-Datei (basierend auf der Auto-Grow-Konfiguration). Im folgenden Bild erhalten wir einen Einblick in die SQL Server-Transaktionsprotokolldatei:

Im Fall einer einzelnen Transaktionsprotokolldatei verwendet SQL Server einen kreisförmigen virtuellen Protokolldateipfad. Wir können die Anzahl der virtuellen Protokolldateien und ihren Status wie folgt überprüfen:

1. DBCC LOGINFO(‚Database‘) – Es ist eine alte Anweisung und funktioniert mit allen SQL Server-Versionen
2. Dynamic Management view sys.dm_db_log_info (DBID). Es ist ab SQL Server 2016 SP2 oder höher verfügbar

Jeder der Befehle kann für die VLF-Prüfung verwendet werden. In diesem Artikel verwenden wir die dynamische Verwaltungsansicht (DMV):

Im obigen Screenshot haben wir überprüft, dass unsere Beispieldatenbank zwei Transaktionsprotokolldateien enthält (file_id 2 und file_id 3).

• Datei-ID 2 hat aktiven VLF (vlf_active=1 und vlf_status=2)
• Datei-ID 3 hat keinen aktiven VLF (vlf_active=0 und vlf_status=2)

Fügen wir einige weitere Datensätze in die Tabelle ein, damit sich der aktive VLF ändert:

Die Überwachung der Transaktionslogspeicherplatznutzung und des VLF-Status erfolgt mit DMV (sys.dm_db_log_space_usage):

• VLF-Status

  1 2	WÄHLEN SIE DB_name() als Datenbankname,File_ID als transaction_log_file_ID, vlf_active , vlf_status AUS sys.dm_db_log_info(DB_ID(‚MultileLogFiles‘));

• Transaktionsprotokollnutzung (verwendeter und freier Speicherplatz)

  1 2 3 4 5

  WÄHLEN SIE total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB, verwendet_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB, (total_log_size_in_bytes – used_log_space_in_bytes)*1.0/1024/1024 ALS free_log_space_in_MB VON sys.dm_db_log_space_usage;

Wir erhalten die folgende Ausgabe der obigen Abfragen:

• Auf der linken Seite sehen wir, dass die primäre Protokolldatei (file_id 2), sobald sie voll ist, zur nächsten Protokolldatei (file_id 3) wechselt. Zu diesem Zeitpunkt haben wir aktives VLF sowohl in primären als auch in sekundären Protokolldateien. In einer Datenbank mit einem vollständigen Wiederherstellungsmodell benötigen wir ein Transaktionsprotokoll-Backup, damit VLF als inaktiv markiert wird
• Die rechte Ausgabe (mit DMV) zeigt die verwendete Transaktionsprotokolldatei mit 8,51 MB an. Wie Sie sich erinnern, haben wir für jede Transaktionsprotokolldatei eine Größe von 8 MB festgelegt und das automatische Protokollwachstum deaktiviert. Wenn die primäre Protokolldatei voll ist (8 MB), wechselt SQL Server zur sekundären Transaktionsprotokolldatei

Sekundäre SQL Server-Transaktionsprotokolldatei entfernen

Um die sekundäre Transaktionsprotokolldatei (Datei-ID 3) zu entfernen, verwenden wir die bearbeitete Anweisung Alter database.

Wir fügen die REMOVE FILE Klausel hinzu und geben die Datei an, die wir entfernen möchten:

Die Ausführung dieser Anweisung führt zu der folgenden Fehlermeldung:

Hinweis: Die aktive Transaktionslogdatei kann nicht entfernt werden.

Zuvor haben wir gesehen, dass SQL Server die sekundäre Protokolldatei verwendet, sobald die primäre Protokolldatei voll ist. Wir müssen ein sekundäres Transaktionsprotokoll leer machen, damit wir es entfernen können.

In der SQL-Datenbank mit einem vollständigen Wiederherstellungsmodell verwenden wir Transaktionsprotokollsicherungen, damit SQL Server die Protokolle abschneiden kann. Abhängig von der Größe des Transaktionsprotokolls, dem aktiven VLF und der aktiven Transaktion benötigen wir möglicherweise mehrere Protokollsicherungen.

Führen wir eine vollständige Datenbank- und Transaktionsprotokollsicherung durch:

• Vollständige Datenbanksicherung

  1	Backup-Datenbank MultipleLogFiles auf Festplatte =’C:\Temp\MultipleLogFiles.bak‘

• Transaktionsprotokollsicherung

  1	log MultipleLogFiles auf Festplatte sichern=’C:\Temp\MultipleLogFiles_log.trn‘

Überprüfen Sie nach Abschluss der Sicherung den VLF-Status. Das aktive VLF sollte sich in der ersten Protokolldatei (primär) befinden, damit wir die sekundäre Protokolldatei entfernen können. Wir haben überprüft, dass nur die primäre Protokolldatei (file_id 2) aktiv ist (VLF-Status 2):

Nun sollte die sekundäre Transaktionsprotokolldatei ohne Probleme entfernt werden. Führen wir die bearbeitete Alter database-Anweisung erneut aus. Die sekundäre Transaktionsprotokolldatei wird entfernt:

Sobald die sekundäre Transaktionsprotokolldatei entfernt wurde, überprüfen Sie sie mithilfe der GUI sowie von T-SQL mit der Systemansicht sys.Datenbankdatei:

Die entfernte sekundäre Transaktionsprotokolldatei ist gemäß dem folgenden Screenshot weiterhin vorhanden:

Klicken Sie nun mit der rechten Maustaste auf die Datenbank und zeigen Sie vorhandene Dateien an. Wir sehen die entfernte Transaktionsprotokolldatei auch in der GUI. Aber warum?

Führen wir die bearbeitete Alter database-Anweisung erneut aus und prüfen Sie, ob SQL Server die Transaktionsprotokolldatei erneut entfernt.

Wir haben die Meldung erhalten, dass SQL Server die angegebene Protokolldatei nicht finden konnte:

SQL Server entfernt die Transaktionslogdatei nach einer nachfolgenden Protokollsicherung. Lassen Sie uns eine weitere Protokollsicherung durchführen und überprüfen, ob die Transaktionsprotokolldatei noch vorhanden ist:

Überprüfen Sie die Transaktionsprotokolldatei sowohl in der GUI- als auch in der T-SQL-Methode. Wir sehen, dass die entfernte Transaktionsprotokolldatei jetzt nicht angezeigt wird.

Fazit

In diesem Artikel haben wir die Verwendung des sekundären SQL Server-Transaktionslogs und dessen Entfernung untersucht. Sie sollten vermeiden, mehrere Transaktionsprotokolldateien zu verwenden, insbesondere in der Produktionsdatenbank. Sie sollten vor der Planung einer Aktivität eine Datenbanksicherung durchführen und diese in nicht produktiven Stunden durchführen.

• Autor
• Neueste Beiträge

Als MCSA Certified und Microsoft Certified Trainer in Gurgaon, Indien, mit 13 Jahren Erfahrung, Rajendra arbeitet für eine Vielzahl von großen Unternehmen mit Schwerpunkt auf Performance-Optimierung, Überwachung, Hochverfügbarkeit, und Disaster Recovery-Strategien und Umsetzung. Er ist Autor hunderter maßgeblicher Artikel zu SQL Server, Azure, MySQL, Linux, Power BI, Performance Tuning, AWS / Amazon RDS, Git und verwandten Technologien, die bisher von über 10 Millionen Lesern angesehen wurden.
Er ist der Schöpfer einer der größten kostenlosen Online-Sammlungen von Artikeln zu einem einzigen Thema, mit seiner 50-teiligen Serie über SQL Server Always On Availability Groups. Basierend auf seinem Beitrag zur SQL Server-Community wurde er 2020 und 2021 bei SQLShack mit verschiedenen Auszeichnungen ausgezeichnet, darunter dem renommierten „Best author of the year“.
Raj ist immer an neuen Herausforderungen interessiert, wenn Sie also Beratungshilfe zu einem Thema benötigen, das in seinen Schriften behandelt wird, kann er bei Rajendra erreicht [email protected]
Alle Beiträge von Rajendra Gupta anzeigen

Neueste Beiträge von Rajendra Gupta (alle anzeigen)

• Verwenden von ARM-Vorlagen zum Bereitstellen von Azure-Containerinstanzen mit SQL Server-Linux-Images – 21. Dezember 2021
• Remotedesktopzugriff für AWS RDS SQL Server mit Amazon RDS Custom – 14. Dezember 2021
• Speichern von SQL Server-Dateien im persistenten Speicher für Azure-Containerinstanzen – Dezember 10, 2021