Das Transaktionsprotokoll [SQL Server]

Artikel
01/04/2017

Jede SQL Server-Datenbank verfügt über ein Transaktionsprotokoll, in dem alle Transaktionen sowie die Datenbankänderungen aufgezeichnet werden, die von den einzelnen Transaktionen vorgenommen werden. Um das Überlaufen des Transaktionsprotokolls zu verhindern, muss es in regelmäßigen Abständen gekürzt werden. Einige Faktoren können die Protokollkürzung jedoch verzögern, sodass die Überwachung der Protokollgröße wichtig ist. Einige Vorgänge lassen sich minimal protokollieren, um deren Auswirkung auf die Größe des Transaktionsprotokolls zu reduzieren.

Das Transaktionsprotokoll ist eine wichtige Komponente der Datenbank und wird im Falle eines Systemfehlers ggf. benötigt, um einen konsistenten Status der Datenbank wiederherzustellen. Das Transaktionsprotokoll sollte nur dann gelöscht oder verschoben werden, wenn die Auswirkungen dieses Vorgangs vollständig bekannt sind.

Hinweis

Einige bekannte gute Ausgangspunkte für das Anwenden von Transaktionsprotokollen während der Datenbankwiederherstellung werden durch Prüfpunkte vorgegeben. Weitere Informationen finden Sie unter Datenbankprüfpunkte (SQL Server).

In diesem Thema:

Vorteile: Vom Transaktionsprotokoll unterstützte Vorgänge
Transaktionsprotokollabkürzung
Faktoren, die die Protokollabkürzung verzögern können
Vorgänge, die minimal protokolliert werden können
Verwandte Aufgaben

Vorteile: Vom Transaktionsprotokoll unterstützte Vorgänge

Das Transaktionsprotokoll unterstützt die folgenden Vorgänge:

Wiederherstellen einzelner Transaktionen.
Wiederherstellen aller unvollständigen Transaktionen, wenn SQL Server gestartet wird.
Ausführen eines Rollforwards für eine wiederhergestellte Datenbank, Datei, Dateigruppe oder Seite bis zu dem Punkt, an dem der Fehler aufgetreten ist.
Unterstützen der Transaktionsreplikation.
Unterstützung von Hochverfügbarkeits- und Notfallwiederherstellungslösungen: Always On Verfügbarkeitsgruppen, Datenbankspiegelung und Protokollversand.

Transaktionsprotokollabschneidungen

Durch das Kürzen des Protokolls wird in der Protokolldatei Speicherplatz freigegeben, der vom Transaktionsprotokoll erneut verwendet werden kann. Die Protokollkürzung ist wichtig, um ein Auffüllen des Protokolls verhindern zu können. Die Protokollabkürzung löscht inaktive virtuelle Protokolldateien aus dem logischen Transaktionsprotokoll einer SQL Server Datenbank, wodurch Speicherplatz im logischen Protokoll für die Wiederverwendung durch das Physische Transaktionsprotokoll frei wird. Wird ein Transaktionsprotokoll nicht gekürzt, füllt sich dadurch möglicherweise der gesamte Speicherplatz des Datenträgers auf, der den zugehörigen physischen Protokolldateien zugeordnet ist.

Um dieses Problem zu vermeiden, erfolgt die Kürzung automatisch nach den folgenden Ereignissen, sofern die Protokollkürzung nicht aus bestimmten Gründen verzögert wird:

Unter dem einfachen Wiederherstellungsmodell, nach einem Prüfpunkt.
Unter dem vollständigen oder massenprotokollierten Wiederherstellungsmodell, wenn ein Prüfpunkt seit der vorherigen Sicherung ausgelöst wurde, erfolgt die Kürzung nach einer Protokollsicherung (sofern es sich nicht um eine Kopiesicherung handelt).

Weitere Informationen finden Sie weiter unten in diesem Thema unter Faktoren, die die Protokollkürzung verzögern können.

Hinweis

Die Protokollkürzung verringert nicht die Größe einer physischen Protokolldatei. Sie müssen zum Reduzieren der physischen Größe einer physischen Protokolldatei die Protokolldatei verkleinern. Informationen zum Verkleinern der Größe der physischen Protokolldatei finden Sie unter Verwalten der Größe der Transaktionsprotokolldatei.

Faktoren, die die Protokollkürzung verzögern können

Bleiben Protokolldatensätze lange aktiv, verzögert sich die Transaktionsprotokollkürzung. Dabei kann sich das Transaktionsprotokoll potenziell auffüllen.

Wichtig

Informationen zum Umgang mit einem vollen Transaktionsprotokoll finden Sie unter Problembehandlung bei einem vollen Transaktionsprotokoll (SQL Server-Fehler 9002).

Die Protokollkürzung kann durch verschiedene Faktoren verzögert werden. Sie können ermitteln, wodurch die Protokollkürzung verhindert wird, indem Sie die Spalten log_reuse_wait und log_reuse_wait_desc der Katalogsicht sys.databases abfragen. In der folgenden Tabelle werden die Werte dieser Spalten beschrieben.

log_reuse_wait value	log_reuse_wait_desc value	BESCHREIBUNG
0	NOTHING	Derzeit ist mindestens eine wiederverwendbare virtuelle Protokolldatei vorhanden.
1	CHECKPOINT	Seit der letzten Protokollkürzung ist kein Prüfpunkt aufgetreten, oder der Kopf des Protokolls wurde noch nicht über eine virtuelle Protokolldatei hinaus verschoben. (Alle Wiederherstellungsmodelle) Dies ist ein häufiger Grund für das verzögerte Kürzen von Protokollen. Weitere Informationen finden Sie unter Datenbankprüfpunkte (SQL Server).
2	LOG_BACKUP	Eine Protokollsicherung ist erforderlich, bevor das Transaktionsprotokoll gekürzt werden kann. (nur vollständiges bzw. massenprotokolliertes Wiederherstellungsmodell) Bei Abschluss der nächsten Protokollsicherung wird möglicherweise ein Teil des Protokollspeicherplatzes zur Wiederverwendung freigegeben.
3	ACTIVE_BACKUP_OR_RESTORE	Es findet gerade eine Datensicherung oder eine Wiederherstellung statt (alle Wiederherstellungsmodelle). Verhindert eine Datensicherung die Protokollkürzung, kann das unmittelbare Problem u. U. durch Abbrechen des Sicherungsvorgangs behoben werden.
4	ACTIVE_TRANSACTION	Eine Transaktion ist aktiv (alle Wiederherstellungsmodelle). Möglicherweise ist beim Starten der Protokollsicherung eine Transaktion mit langer Ausführungszeit vorhanden. In diesem Fall ist zum Freigeben von Speicherplatz möglicherweise eine weitere Protokollsicherung erforderlich. Beachten Sie, dass transaktionen mit langer Ausführungszeit die Protokollabkürzung unter allen Wiederherstellungsmodellen verhindern, einschließlich des einfachen Wiederherstellungsmodells, bei dem das Transaktionsprotokoll in der Regel für jeden automatischen Prüfpunkt abgeschnitten wird. Eine Transaktion wird verzögert. Eine verzögerte Transaktion ist tatsächlich eine aktive Transaktion, deren Rollback aufgrund einer nicht verfügbaren Ressource blockiert ist. Weitere Informationen zu den Ursachen für verzögerte Transaktionen und zum Auflösen ihres verzögerten Zustands finden Sie unter Verzögerte Transaktionen (SQL Server). Lang andauernde Transaktionen können auch das Transaktionsprotokoll von „tempdb“ füllen. „tempdb“ wird implizit von Benutzertransaktionen für interne Objekte wie z.B. Arbeitstabellen zum Sortieren, Arbeitsdateien für Hashverfahren, Cursorarbeitstabellen und Zeilenversionsverwaltung verwendet. Auch wenn die Benutzertransaktion nur Lesedaten (SELECT-Abfragen) enthält, können interne Objekte unter Benutzertransaktionen erstellt und verwendet werden. Anschließend kann das tempdb-Transaktionsprotokoll gefüllt werden.
5	DATABASE_MIRRORING	Die Datenbankspiegelung wurde angehalten, oder im Modus für hohe Leistung befindet sich die Spiegeldatenbank deutlich hinter der Prinzipaldatenbank. (nur vollständiges Wiederherstellungsmodell) Weitere Informationen finden Sie unter Datenbankspiegelung (SQL Server).
6	REPLIKATION	Während der Transaktionsreplikationen wurden für die Veröffentlichungen relevante Transaktionen noch immer nicht für die Verteilungsdatenbank bereitgestellt. (nur vollständiges Wiederherstellungsmodell) Weitere Informationen zur Transaktionsreplikation finden Sie unter SQL Server Replication.
7	DATABASE_SNAPSHOT_CREATION	Eine Datenbank-Momentaufnahme wird erstellt. (Alle Wiederherstellungsmodelle) Dies ist ein häufiger, im Allgemeinen jedoch nur kurz andauernder Grund für ein verzögertes Kürzen eines Protokolls.
8	LOG_SCAN	Ein Protokollscan wird ausgelöst. (Alle Wiederherstellungsmodelle) Dies ist ein häufiger, im Allgemeinen jedoch nur kurz andauernder Grund für ein verzögertes Kürzen eines Protokolls.
9	AVAILABILITY_REPLICA	Ein sekundäres Replikat einer Verfügbarkeitsgruppe wendet Transaktionsprotokoll-Datensätze dieser Datenbank auf eine zugehörige sekundäre Datenbank an. (vollständiges Wiederherstellungsmodell) Weitere Informationen finden Sie unter Übersicht über AlwaysOn-Verfügbarkeitsgruppen (SQL Server).
10	-	Nur interne Verwendung
11	-	Nur interne Verwendung
12	-	Nur interne Verwendung
13	OLDEST_PAGE	Ist eine Datenbank zur Verwendung von indirekten Prüfpunkten konfiguriert, ist die älteste Seite in der Datenbank u. U. älter als die Prüfpunkt-LSN. In diesem Fall kann die älteste Seite die Protokollkürzung verzögern. (Alle Wiederherstellungsmodelle) Weitere Informationen zu indirekten Prüfpunkten finden Sie unter Datenbankprüfpunkte (SQL Server).
14	OTHER_TRANSIENT	Dieser Wert wird derzeit nicht verwendet.
16	XTP_CHECKPOINT	Wenn eine Datenbank über eine speicheroptimierte Dateigruppe verfügt, wird das Transaktionsprotokoll möglicherweise erst abgeschnitten, wenn die automatische In-Memory OLTP-Prüfpunkt ausgelöst wird (dies geschieht bei jeder Protokollvergrößerung von 512 MB). Hinweis: Um das Transaktionsprotokoll vor einer Größe von 512 MB abzuschneiden, führen Sie den Befehl Prüfpunkt manuell für die betreffende Datenbank aus.

Vorgänge, für die eine minimale Protokollierung verfügbar ist

Bei derminimalen Protokollierung werden nur die Informationen protokolliert, die zum Wiederherstellen der Transaktion ohne Unterstützung der Zeitpunktwiederherstellung erforderlich sind. In diesem Thema werden die Vorgänge aufgeführt, die unter dem massenprotokollierten Wiederherstellungsmodell minimal protokolliert werden (sowie unter dem einfachen Wiederherstellungsmodell, es sei denn, es wird eine Sicherung ausgeführt).

Hinweis

Die minimale Protokollierung wird für speicheroptimierte Tabellen nicht unterstützt.

Hinweis

Unter dem vollständigen Wiederherstellungsmodell werden alle Massenvorgänge vollständig protokolliert. Sie können die Protokollierung für eine Reihe von Massenvorgängen jedoch verringern, indem Sie die Datenbank bei Massenvorgängen vorübergehend in das massenprotokollierte Wiederherstellungsmodell schalten. Die minimale Protokollierung ist effizienter als die vollständige Protokollierung und senkt die Wahrscheinlichkeit, dass ein umfangreicher Massenvorgang den verfügbaren Transaktionsprotokoll-Speicherplatz während einer Massentransaktion auffüllt. Wenn die Datenbank bei Aktivierung der minimalen Protokollierung jedoch beschädigt wird oder verloren geht, können Sie die Datenbank nicht bis zu dem Punkt wiederherstellen, an dem der Fehler aufgetreten ist.

Die folgenden Vorgänge, die unter dem vollständigen Wiederherstellungsmodell vollständig protokolliert werden, werden unter dem einfachen und massenprotokollierten Wiederherstellungsmodell minimal protokolliert:

Massenimportvorgänge (bcp, BULK INSERT und INSERT... SELECT). Weitere Informationen zur minimalen Protokollierung eines Massenimports in eine Tabelle finden Sie unter Prerequisites for Minimal Logging in Bulk Import.

Hinweis

Wenn die Transaktionsreplikation aktiviert ist, werden BULK INSERT-Vorgänge auch unter dem massenprotokollierten Wiederherstellungsmodell vollständig protokolliert.
SELECT INTO-Vorgänge .

Hinweis

Wenn die Transaktionsreplikation aktiviert ist, werden SELECT INTO-Vorgänge auch unter dem massenprotokollierten Wiederherstellungsmodell vollständig protokolliert.
Teilupdates von Datentypen für hohe Werte mithilfe der .WRITE-Klausel in der UPDATE -Anweisung beim Einfügen oder Anfügen neuer Daten. Beachten Sie, dass die minimale Protokollierung nicht verwendet wird, wenn vorhandene Werte aktualisiert werden. Weitere Informationen zu Datentypen für hohe Werte finden Sie unter Datentypen (Transact-SQL).
WRITETEXT - und UPDATETEXT-Anweisungen beim Einfügen oder Anfügen neuer Daten in die textDatentypspalten , ntextund image . Beachten Sie, dass die minimale Protokollierung nicht verwendet wird, wenn vorhandene Werte aktualisiert werden.

Hinweis

Die WRITETEXT-Anweisung und UPDATETEXT-Anweisung sind als veraltet markiert, sollten also in neuen Anwendungen nicht mehr verwendet werden.
Wenn für die Datenbank das einfache oder massenprotokollierte Wiederherstellungsmodell festgelegt ist, werden einige Index-DDL-Vorgänge minimal protokolliert, unabhängig davon, ob der Vorgang offline oder online ausgeführt wird. Die minimal protokollierten Indexvorgänge sind nachfolgend aufgeführt:
- CREATE INDEX -Vorgänge (einschließlich indizierter Sichten).
- ALTER INDEX REBUILD- oder DBCC DBREINDEX-Vorgänge.
  
  Hinweis
  
  Die DBCC DBREINDEX-Anweisung ist als veraltet markiert, sollte also in neuen Anwendungen nicht mehr verwendet werden.
- Neuerstellungen neuer Heaps mit DROP INDEX (falls zutreffend).
  
  Hinweis
  
  Aufhebungen von Indexseitenzuordnungen während eines DROP INDEX -Vorgangs werden immer vollständig protokolliert.