The Transaction Log (SQL Server) (Deutsch)

• 10/23/2019
• 11 minutes to read
• • M
  • D
  • f
  • M
  • s
  • +8

Applies to: SQL Server (all supported versions)

Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction.,

Das Transaktionsprotokoll ist eine kritische Komponente der Datenbank. Wenn ein Systemfehler vorliegt, benötigen Sie dieses Protokoll, um Ihre Datenbank wieder in einen konsistenten Zustand zu versetzen.

Informationen zur Transaktionsprotokollarchitektur und-interna finden Sie im SQL Server Transaktionsprotokollarchitektur-und-Managementhandbuch.

Operationen, die vom Transaktionsprotokoll unterstützt werden

Das Transaktionsprotokoll unterstützt die folgenden Operationen:

• Wiederherstellung einzelner Transaktionen.
• Wiederherstellung aller unvollständigen Transaktionen, wenn SQL Server gestartet wird.
• Rollen einer wiederhergestellten Datenbank, Datei,Dateigruppe oder Seite bis zum Fehlerpunkt.
• Unterstützung der Transaktionsreplikation.
• Unterstützung von Hochverfügbarkeits-und Disaster-Recovery-Lösungen: Immer auf Verfügbarkeitsgruppen, Datenbankspiegelung und Protokollversand.,

Wiederherstellung einzelner Transaktionen

Wenn eine Anwendung eine ROLLBACK – Anweisung ausgibt oder wenn die Datenbank-Engine einen Fehler wie den Verlust der Kommunikation mit einem Client erkennt, werden die Protokolldatensätze verwendet, um die durch eine unvollständige Transaktion vorgenommenen Änderungen zurückzusetzen.,

Wiederherstellung aller unvollständigen Transaktionen beim Starten von SQL Server

Wenn ein Server ausfällt, bleiben die Datenbanken möglicherweise in einem Zustand, in dem einige Änderungen nie aus dem Puffercache in die Datendateien geschrieben wurden.und es kann einige Änderungen von unvollständigen Transaktionen in den Datendateien geben. Wenn eine Instanz von SQL Server gestartet wird, wird eine Wiederherstellung jeder Datenbank ausgeführt. Jede im Protokoll aufgezeichnete Änderung, die möglicherweise nicht in die Datendateien geschrieben wurde, wird vorgerollt., Jede unvollständige Transaktion, die im Transaktionsprotokoll gefunden wird, wird dann zurückgesetzt, um sicherzustellen, dass die Integrität der Datenbank erhalten bleibt. Weitere Informationen finden Sie unter Wiederherstellen und Wiederherstellen Übersicht (SQL Server).

Eine wiederhergestellte Datenbank, Datei, Dateigruppe oder Seite bis zum Fehlerpunkt weiterleiten

Nach einem Hardwareverlust oder Festplattenausfall, der sich auf die Datenbankdateien auswirkt, können Sie die Datenbank bis zum Fehlerpunkt wiederherstellen., Sie stellen zuerst die letzte vollständige Datenbanksicherung und die letzte differentielle Datenbanksicherung wieder her und stellen dann die nachfolgende Sequenz der Transaktionsprotokollsicherungen bis zum Fehlerpunkt wieder her.

Wenn Sie jede Protokollsicherung wiederherstellen, wendet das Datenbankmodul alle im Protokoll aufgezeichneten Änderungen erneut an, um alle Transaktionen weiterzuleiten. Wenn die letzte Protokollsicherung wiederhergestellt ist, verwendet das Datenbankmodul die Protokollinformationen, um alle Transaktionen zurückzusetzen, die zu diesem Zeitpunkt noch nicht abgeschlossen waren. Weitere Informationen finden Sie unter Wiederherstellen und Wiederherstellen Übersicht (SQL Server).,

Unterstützung der Transaktionsreplikation

Der Log Reader Agent überwacht das Transaktionsprotokoll jeder für die Transaktionsreplikation konfigurierten Datenbank und kopiert die für die Replikation markierten Transaktionen aus dem Transaktionslog in die Verteilungsdatenbank. Weitere Informationen finden Sie unter Funktionsweise der Transaktionsreplikation.

Unterstützung von Hochverfügbarkeits-und Disaster-Recovery-Lösungen

Die Standby-Server-Lösungen, immer auf Verfügbarkeitsgruppen, Datenbankspiegelung und Protokollversand, verlassen sich stark auf das Transaktionsprotokoll.,

In einem Always-On-Availability-Groups-Szenario wird jedes Update auf eine Datenbank, das primäre Replikat, sofort in separaten, vollständigen Kopien der Datenbank, den sekundären Replikaten, reproduziert. Das primäre Replikat sendet jeden Protokolldatensatz sofort an die sekundären Replikate, die die eingehenden Protokolldatensätze auf Verfügbarkeitsgruppendatenbanken anwenden und kontinuierlich weiterleiten. Weitere Informationen finden Sie unter Immer auf Failover-Cluster-Instanzen

In einem Protokollversandszenario sendet der primäre Server das aktive Transaktionsprotokoll der primären Datenbank an ein oder mehrere Ziele., Jeder sekundäre Server stellt das Protokoll in seiner lokalen sekundären Datenbank wieder her. Weitere Informationen finden Sie unter Über Log Versand.

In einem Datenbankspiegelungsszenario wird jede Aktualisierung einer Datenbank, der Hauptdatenbank, sofort in einer separaten, vollständigen Kopie der Datenbank, der Spiegeldatenbank, reproduziert. Die Hauptserverinstanz sendet jeden Protokolldatensatz sofort an die Spiegelserverinstanz, die die eingehenden Protokolldatensätze auf die Spiegeldatenbank anwendet und sie kontinuierlich weiterleitet. Weitere Informationen finden Sie unter Datenbankspiegelung.,

Transaction-log-Merkmale

Eigenschaften des SQL Server-Datenbank-Engine transaction log:

• Das Transaktionsprotokoll ist implementiert als eine separate Datei oder Gruppe von Dateien in die Datenbank. Der Protokollcache wird getrennt vom Puffercache für Datenseiten verwaltet, was zu einfachem, schnellem und robustem Code innerhalb der SQL Server-Datenbank-Engine führt. Weitere Informationen finden Sie unter Physikalische Architektur des Transaktionsprotokolls.

• Das Format von Protokolldatensätzen und-seiten ist nicht auf das Format von Datenseiten beschränkt.,

• Das Transaktionsprotokoll kann in mehreren Dateien implementiert werden. Die Dateien können so definiert werden, dass sie automatisch erweitert werden, indem der Wert FILEGROWTH für das Protokoll festgelegt wird. Dies reduziert das Potenzial, dass im Transaktionsprotokoll kein Speicherplatz mehr zur Verfügung steht, und reduziert gleichzeitig den Verwaltungsaufwand. Weitere Informationen finden Sie unter Datei-und Dateigruppenoptionen ÄNDERN der DATENBANK (Transact-SQL).

• Der Mechanismus zur Wiederverwendung des Speicherplatzes in den Protokolldateien ist schnell und hat nur minimale Auswirkungen auf den Transaktionsdurchsatz.,

Informationen zur Transaktionsprotokollarchitektur und-interna finden Sie im SQL Server Transaktionsprotokollarchitektur-und-Managementhandbuch.

Transaction log truncation

Log truncation gibt Speicherplatz in der Protokolldatei für die Wiederverwendung durch das Transaction Log frei. Sie müssen Ihr Transaktionsprotokoll regelmäßig abschneiden, damit es den zugewiesenen Speicherplatz nicht ausfüllt. Mehrere Faktoren können die Protokollverkürzung verzögern, daher ist die Überwachung der Protokollgröße von Bedeutung. Einige Vorgänge können minimal protokolliert werden, um ihre Auswirkungen auf die Transaktionsprotokollgröße zu verringern.,

Log Truncation löscht inaktive virtuelle Protokolldateien (VLFs) aus dem logischen Transaktionsprotokoll einer SQL Server-Datenbank, wodurch Speicherplatz im logischen Protokoll für die Wiederverwendung durch das physische Transaktionsprotokoll freigegeben wird. Wenn ein Transaktionsprotokoll niemals abgeschnitten wird, füllt es schließlich den gesamten Speicherplatz, der physischen Protokolldateien zugewiesen ist.

Um zu vermeiden, dass der Speicherplatz knapp wird, erfolgt das Abschneiden der Protokolle aus irgendeinem Grund nicht automatisch nach den folgenden Ereignissen:

• Unter dem einfachen Wiederherstellungsmodell nach einem Prüfpunkt.,
• Wenn im Rahmen des vollständigen Wiederherstellungsmodells oder des Massenprotokoll-Wiederherstellungsmodells seit der vorherigen Sicherung ein Prüfpunkt aufgetreten ist, erfolgt die Kürzung nach einer Protokollsicherung (es sei denn, es handelt sich um eine Nur-Kopie-Protokollsicherung).

weitere Informationen finden Sie unter Faktoren, die Protokollkürzung verzögern können, später in diesem Thema.

Faktoren, die das Abschneiden von Protokollen verzögern können

Wenn Protokolldatensätze lange aktiv bleiben, wird das Abschneiden von Transaktionsprotokollen verzögert und das Transaktionsprotokoll kann sich füllen, wie wir bereits in diesem langen Thema erwähnt haben.,

Tatsächlich kann das Abschneiden von Protokollen aus verschiedenen Gründen verzögert werden. Erfahren Sie, was, wenn überhaupt, Ihre Protokollverkürzung verhindert, indem Sie die Spalten log_reuse_wait und log_reuse_wait_desc des Systems log_reuse_wait_desc.datenbanken Katalog-Ansicht. Die folgende Tabelle beschreibt die Werte dieser Spalten.,

Operationen, die minimal protokolliert werden können

Minimale Protokollierung beinhaltet nur die Protokollierung der Informationen, die zum Wiederherstellen erforderlich sind die Transaktion ohne Unterstützung der Punkt-in-Zeit-Wiederherstellung., In diesem Thema werden die Vorgänge identifiziert, die unter dem Massenprotokoll-Wiederherstellungsmodell (sowie unter dem einfachen Wiederherstellungsmodell, außer wenn eine Sicherung ausgeführt wird) minimal protokolliert werden.

Die folgenden Vorgänge, die im vollständigen Wiederherstellungsmodell vollständig protokolliert sind, werden im einfachen und im Massenprotokoll protokollierten Wiederherstellungsmodell minimal protokolliert:

• Massenimportvorgänge (bcp, BULK INSERT und INSERT… WÄHLEN)., Weitere Informationen darüber, wann der Massenimport in eine Tabelle minimal protokolliert wird, finden Sie unter Voraussetzungen für die minimale Protokollierung im Massenimport.

Wenn die Transaktionsreplikation aktiviert ist, werdenBULK INSERT Vorgänge auch unter dem Bulk Logged Recovery-Modell vollständig protokolliert.

• WÄHLEN Sie IN Betrieb.

Wenn die Transaktionsreplikation aktiviert ist, werdenSELECT INTO Vorgänge auch unter dem Bulk Logged Recovery-Modell vollständig protokolliert.,

• Teilweise Aktualisierungen von Datentypen mit großen Werten unter Verwendung der.WRITE – Klausel in der UPDATE-Anweisung beim Einfügen oder Anhängen neuer Daten. Beachten Sie, dass bei der Aktualisierung vorhandener Werte keine minimale Protokollierung verwendet wird. Weitere Informationen zu Datentypen mit großem Wert finden Sie unter Datentypen (Transact-SQL).

• WRITETEXT-und UPDATETEXT-Anweisungen beim Einfügen oder Anhängen neuer Daten in die Spalten Text, ntext und Bilddatentyp. Beachten Sie, dass bei der Aktualisierung vorhandener Werte keine minimale Protokollierung verwendet wird.,

  Warnung

  DieWRITETEXT undUPDATETEXT Anweisungen sind veraltet; vermeiden Sie die Verwendung in neuen Anwendungen.

• Wenn die Datenbank auf das einfache oder Bulk-logged Recovery-Modell eingestellt ist, werden einige Index-DDL-Operationen minimal protokolliert, unabhängig davon, ob der Vorgang offline oder online ausgeführt wird. Die minimal protokollierten Indexoperationen lauten wie folgt:

  • Indexoperationen erstellen (einschließlich indizierter Ansichten).

  • ÄNDERN INDEX REBUILD oder DBCC DBEINDEX Operationen.,

    Warnung

    DieDBCC DBREINDEX Anweisung ist veraltet; Verwenden Sie sie nicht in neuen Anwendungen.

    Hinweis

    Index-Build-Operationen verwenden eine minimale Protokollierung, können sich jedoch verzögern, wenn gleichzeitig eine Sicherung ausgeführt wird. Diese Verzögerung wird durch die Synchronisierungsanforderungen von minimal protokollierten Pufferpoolseiten bei Verwendung des einfachen oder bulk-protokollierten Wiederherstellungsmodells verursacht.

  • INDEX LÖSCHEN neuer Heap-Rebuild (falls zutreffend). Index page deallocation während ein DROP INDEX Bedienung ist immer vollständig protokolliert.,n die Datenbank ist beschädigt (SQL Server)

  Wiederherstellen des Transaktionsprotokolls (vollständiges Wiederherstellungsmodell)

  • Wiederherstellen einer Transaktionsprotokollsicherung (SQL Server)

  Siehe auch

  SQL Server Transaction Log Architecture and Management Guide
  Kontrolle der Transaktionsbeständigkeit
  Voraussetzungen für minimale Protokollierung im Massenimport
  Sichern und Wiederherstellen von SQL Server-Datenbanken
  Wiederherstellen und Wiederherstellen Übersicht (SQL Server)
  Datenbankprüfpunkte (SQL Server Server)
  Anzeigen oder Ändern Sie die Eigenschaften einer Datenbank
  Recovery-Modelle (SQL Server)
  Transaction Log Backups (SQL Server)
  sys.,dm_db_log_info (Transact-SQL)
  sys. dm_db_log_space_usage (Transact-SQL)

log_reuse_wait value	log_reuse_wait_desc value	Beschreibung
0	NOTHING	Derzeit gibt es eine oder mehrere wiederverwendbare virtuelle Protokolldateien (VLFs).
1	CHECKPOINT	Seit dem letzten Protokollabbruch ist kein Checkpoint aufgetreten oder der Kopf des Protokolls ist noch nicht über eine virtuelle Protokolldatei (VLF) hinausgegangen. (Alle Wiederherstellungsmodelle) Dies ist ein häufiger Grund für die Verzögerung abschneiden., Weitere Informationen finden Sie unter Datenbankprüfpunkte (SQL Server).
2	LOG_BACKUP	Bevor das Transaktionsprotokoll abgeschnitten werden kann, ist eine Protokollsicherung erforderlich. (Nur vollständige oder Bulk-protokollierte Wiederherstellungsmodelle) Wenn die nächste Protokollsicherung abgeschlossen ist, kann ein Teil des Protokollspeichers wiederverwendbar sein.
3	ACTIVE_BACKUP_OR_RESTORE	Eine Datensicherung oder eine Wiederherstellung ist im Gange (alle Wiederherstellungsmodelle). Wenn eine Datensicherung verhindert Protokollabkürzung, Abbrechen der Sicherungsvorgang könnte das unmittelbare Problem helfen.,
4	ACTIVE_TRANSACTION	Eine Transaktion ist aktiv (alle Wiederherstellungsmodelle): Eine lang laufende Transaktion kann zu Beginn der Protokollsicherung vorhanden sein. In diesem Fall erfordert das Freigeben des Speicherplatzes möglicherweise eine weitere Protokollsicherung. Beachten Sie, dass lang laufende Transaktionen die Protokollverkürzung unter allen Wiederherstellungsmodellen verhindern, einschließlich des einfachen Wiederherstellungsmodells, unter dem das Transaktionsprotokoll im Allgemeinen an jedem automatischen Prüfpunkt abgeschnitten wird. Eine Transaktion wird aufgeschoben., Eine verzögerte Transaktion ist effektiv eine aktive Transaktion, deren Rollback aufgrund einer nicht verfügbaren Ressource blockiert wird. Informationen zu den Ursachen von verzögerten Transaktionen und zum Verschieben aus dem verzögerten Status finden Sie unter Verzögerte Transaktionen (SQL Server). Lang laufende Transaktionen können auch tempdb Transaktionsprotokoll füllen. Tempdb wird implizit von Benutzertransaktionen für interne Objekte wie Arbeitstabellen zum Sortieren, Arbeitsdateien zum Hashing, Cursor-Arbeitstabellen und Zeilenversionierung verwendet., Selbst wenn die Benutzertransaktion nur Lesedaten enthält (SELECT Abfragen), können interne Objekte unter Benutzertransaktionen erstellt und verwendet werden. Dann kann das tempdb-Transaktionsprotokoll ausgefüllt werden.
5	DATABASE_MIRRORING	Die Datenbankspiegelung wird angehalten oder die Spiegeldatenbank befindet sich im Hochleistungsmodus erheblich hinter der Hauptdatenbank. (Nur vollständiges Wiederherstellungsmodell) Weitere Informationen finden Sie unter Datenbankspiegelung (SQL Server).,
6	REPLIKATION	Bei transaktionalen Replikationen werden die für die Veröffentlichungen relevanten Transaktionen noch nicht an die Verteilungsdatenbank ausgeliefert. (Nur vollständiges Wiederherstellungsmodell) Informationen zur Transaktionsreplikation finden Sie unter SQL Server Replication.
7	DATABASE_SNAPSHOT_CREATION	Ein Datenbank-snapshot erstellt wird. (Alle Recovery-Modelle) Dies ist eine Routine, und in der Regel kurz, Ursache der verzögerten Protokoll abschneiden.
8	LOG_SCAN	Ein log-scan Auftritt., (Alle Recovery-Modelle) Dies ist eine Routine, und in der Regel kurz, Ursache der verzögerten Protokoll abschneiden.
9	AVAILABILITY_REPLICA	Ein sekundäres Replikat einer Verfügbarkeitsgruppe wendet Transaktionsprotokolldatensätze dieser Datenbank auf eine entsprechende sekundäre Datenbank an. (Vollständige Wiederherstellungsmodell) weitere Informationen finden Sie unter Übersicht über Always On-Verfügbarkeitsgruppen (SQL Server).,
10	–	Nur für den internen Gebrauch
11	–	Nur für den internen Gebrauch
12	–	Nur für den internen Gebrauch
13	OLDEST_PAGE	Wenn eine Datenbank für die Verwendung indirekter Prüfpunkte konfiguriert ist, ist die älteste Seite in der Datenbank möglicherweise älter als die Prüfpunktprotokollsequenznummer (LSN). In diesem Fall kann die älteste Seite das Abschneiden des Protokolls verzögern. (Alle Recovery-Modelle) Informationen zu indirekten Checkpoints finden Sie unter Datenbank-Checkpoints (SQL Server).,
14	OTHER_TRANSIENT	Dieser Wert wird derzeit nicht verwendet.
16	XTP_CHECKPOINT	Es muss ein OLTP-Prüfpunkt im Speicher durchgeführt werden.Für speicheroptimierte Tabellen wird ein automatischer Prüfpunkt verwendet, wenn die Transaktionsprotokolldatei größer als 1 wird.,5 GB seit dem letzten Prüfpunkt (enthält sowohl festplattenbasierte als auch speicheroptimierte Tabellen) Weitere Informationen finden Sie unter Prüfpunktoperation für speicheroptimierte Tabellen und (https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/)