Durch: Sergey Gigoyan | Aktualisiert: 2015-11-18 | Bemerkungen (9) | Verwandt: Mehr > Datenbankverwaltung
Problem
SQL Server ermöglicht die Verwendung von mehr als einer Transaktionsprotokolldatei, es stellt sich jedoch die Frage, ob dies erforderlich istund was ist der Vorteil mehrerer Transaktionsprotokolldateien. Bei einigen Entwicklern besteht ein Missverständnis, dass mehrere Transaktionsprotokolldateien die Leistung erhöhen können, da SQL Server sie parallel verwenden kann. SQL Server verwendet derzeit nur eine Transaktionsprotokolldatei, und in diesem Fall besteht keine Parallelität. Manchmal kann mehr als eine Protokolldatei seinfür die Fehlerbehebung erforderlich. Daher ist es normalerweise nicht erforderlich, mehr als eine Protokolldatei zu haben.Betrachten wir einen Fall, in dem unsere Datenbank mehr als eine Protokolldatei enthält und wir nur eine behalten sollten.In diesem Tipp wird beschrieben, wie Sie unnötige Protokolldateien korrekt entfernen und nur eine behalten.
Lösung
Bevor wir veranschaulichen, wie unnötige Protokolldateien entfernt werden, beschreiben wir kurz, wie SQL Server funktioniertmit Protokolldateien: wenn die Datenbank über mehr als eine Protokolldatei verfügt, schreibt SQL Server so lange in die erste, bis sie voll ist, und wechselt dann zur zweiten usw. Nachdem die letzte Protokolldatei vollständig ist, kehrt SQL Server zur ersten zurück und der Zyklus wird fortgesetzt.Wie bereits erwähnt, können jedoch manchmal mehr als eine Protokolldatei erforderlich sein. Zum Beispiel, wenn die Festplatte, auf der sich die Protokolldatei befindetwird voll und wir müssen die zweite Protokolldatei an einem anderen Speicherort erstellen, aber nach der Fehlerbehebung sollten wir die zweite Protokolldatei löschen, da es keinen Sinn hat, mehr als eine Protokolldatei zu haben.Nehmen wir nun an, wir haben eine Datenbank mit zwei Protokolldateien, und unsere Aufgabe besteht darin, die zweite zu entfernen.Das folgende Skript erstellt die TestDB-Datenbank mit zwei Protokolldateien und der TestTable-Tabelle. Um es selbst auszuführen, müssen Sie ersetzen“D:\SQL Daten“ mit einem vorhandenen Ordnerpfad.
USE GOCREATE DATABASE ON PRIMARY ( NAME = N'TestDB', FILENAME = N'D:\SQL DATA\TestDB.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'TestDB_log1', FILENAME = N'D:\SQL DATA\TestDB_log1.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%), ( NAME = N'TestDB_log2', FILENAME = N'D:\SQL DATA\TestDB_log2.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOUSE TestDBGOCREATE TABLE TestTable(ID INT IDENTITY(1,1),Value BIGINT)--Change recovery model for TestDB to FULL (if your model database is in FULL recovery model, TestDB has been already created in this mode)ALTER DATABASE TestDB SET RECOVERY FULL
Lassen Sie uns nun die physische Struktur des Transaktionsprotokolls verstehen: Die Transaktionsprotokolldatei besteht ausvirtuelle Protokolldateien (VLF), die die Verwaltungseinheit in der Transaktionsprotokolldatei darstellen. Dies bedeutet, dass die Datenbank-Engine, wenn sie die Protokolldatei vergrößert oder verkleinert, dies mit vollständigen VLFs tut (z. B. kann sie nicht die Hälfte der VLF verkleinern). Die Größe der virtuellen Protokolldateien sowie ihre Nummer in der physischen Protokolldatei sind nicht festgelegt und werden dynamisch von der Datenbank-Engine verwaltet. Um die Transaktionsprotokolldatei intern zu überwachenwir verwenden den Befehl „DBCC LOGINFO“, der Informationen zu virtuellen Protokolldateien bereitstellt. Im folgenden Skript haben wir diesen Befehl für unsere Datenbank verwendet:
--Getting log files infoDBCC LOGINFO('TestDB')
Und das Ergebnis ist das Folgende:
FileID sind Protokolldatei-IDs für unsere Datenbank. Status zeigt an, dass VLF wiederverwendbar ist odernot (mögliche Werte: 0 – ja, 2 -nein). Wie wir sehen können, gibt es nur einen VLF mitstatus = 2. Jetzt, wenn wir Daten in die Testtabelle einfügen und überwachen, wie Protokolldateien wachsen:
USE TestDBGO--Checking log information before insertionSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files--Inserting data into TestTable;WITH ValueTable AS(SELECT 1 nUNION ALL SELECT n+ 1FROM ValueTableWHERE n
In diesem Beispiel können wir sehen, dass beide Protokolldateien gewachsen sind und jetzt auch VLFs mit „Status = 2“ in der zweiten Protokolldatei (FileID = 3) vorhanden sind:
Jetzt müssen wir TestDB_log2 entfernen.ldf-Datei. Beachten Sie, dass wir nur die sekundären Protokolldateien entfernen können. Das Entfernen der primären Protokolldatei ist von SQL Server nicht zulässig.Jede Datenbank hat nur eine primäre Protokolldatei und die erste Protokolldatei, die im Datenbankerstellungsskript erstellt wird, wird als primär betrachtet.Wenn wir versuchen, die zweite Protokolldatei zu entfernen:
USE masterGO--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
Wir erhalten folgende Nachricht:
Wir können die Transaktionsprotokolldatei nur entfernen, wenn sie leer ist, daher müssen wir sie zuerst leeren it.To tun Sie das, wir sollten das Transaktionsprotokoll sichern. Da unsere „TestDB“ -Datenbank neu erstellt wurde und es keine vollständigen Sicherungen gibt, müssen wir eine vollständige Datenbanksicherung für die TestDB-Datenbank durchführen, wonach wir eine Transaktionsprotokollsicherung durchführen können:
--Full backupBACKUP DATABASE TestDB TO DISK =N'D:\SQL DATA\TestDB.bak'--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'
Die Transaktionsprotokollsicherung schneidet die Protokolldatei ab (es gibt einige Ausnahmen, die außerhalb des Geltungsbereichs von thistip liegen). Die Protokollkürzung löscht inaktive virtuelle Protokolldateien vom Anfang des logischen Protokolls und gibt Speicherplatz in der Protokolldatei frei. Das Abschneiden verringert jedoch nicht die Größe einer physischen Protokolldatei. Es gibt nur Platz frei,der wiederverwendet werden kann. Lassen Sie uns „DBCC LOGINFO“ erneut ausführen:
Wie wir sehen können, gibt es keine virtuellen Protokolldateien im „TestDB_log2.ldf“ -Datei mit Status = 2 und jetzt ist unsere Protokolldatei leer und zum Entfernen bereit:
--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
Die Entfernung ist erfolgreich.
Wenn wir jedoch die Informationen erneut protokollieren, werden wir feststellen, dass die logische Protokolldatei noch vorhanden ist:
--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files
Wenn wir eine weitere Protokollsicherung durchführen, wird die Datei gelöscht:
--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files
Nächste Schritte
- Beachten Sie diesen Tipp, um die Verwendung des Transaktionsprotokolls zu ermitteln und eine nicht benötigte Protokolldatei zu entfernen.
- Schauen Sie sich diese Ressourcen an:
- Datenbankverwaltung / Transaktionslog
- https://technet.microsoft.com/en-us/library/ms191433( v=sql.105).aspx
- https://technet.microsoft.com/en-us/library/ms179355( v=sql.105).aspx
- https://technet.microsoft.com/en-us/library/ms345414( v=sql.105).aspx
Über den Autor
Sergey Gigoyan ist ein Datenbank-Profi mit mehr als 10 Jahren Erfahrung, mit einem Fokus auf Datenbank-Design, Entwicklung, Performance-Tuning, Optimierung, Hochverfügbarkeit, BI und DW-Design.Alle meine Tipps anzeigen
Artikel Zuletzt aktualisiert: 2015-11-18