Verwalten der Größe der Transaktionsprotokolldatei

  • Artikel

Gilt für:SQL Server

Dieser Artikel enthält Informationen zum Überwachen der Größe eines SQL Server-Transaktionsprotokolls, Verkleinern des Transaktionsprotokolls, Hinzufügen zu oder Vergrößern einer Transaktionsprotokolldatei, Optimieren der Wachstumsrate des tempdb-Transaktionsprotokolls und Steuern des Wachstums einer Transaktionsprotokolldatei.

Dieser Artikel bezieht sich auf SQL Server. Obwohl sie sehr ähnlich sind, finden Sie Informationen zum Verwalten der Größe von Transaktionsprotokolldateien in Azure SQL Managed Instance unter Verwalten von Dateispeicher für Datenbanken in Azure SQL Managed Instance. Informationen zur Azure SQL Datenbank finden Sie unter Verwalten von Dateispeicher für Datenbanken in der Azure SQL-Datenbank.

Grundlegendes zu den Arten von Speicherplatz für eine Datenbank

Es wichtig, dass Sie mit den folgenden Speicherplatzmengen vertraut sind, damit Sie den Dateispeicherplatz einer Datenbank verwalten können.

Datenbankmenge Definition Kommentare
Genutzter Speicherplatz Der Speicherplatz, der zum Speichern von Datenbankdaten verwendet wird. In der Regel erhöht (verringert) sich der Platzbedarf bei eingefügten (gelöschten) Daten. In manchen Fällen ändert sich der genutzte Speicherplatz beim Einfügen oder Löschen von Daten nicht, je nach Menge und Muster der an dem Vorgang beteiligten Daten und einer eventuellen Fragmentierung. Beispielsweise wird der genutzte Speicherplatz durch Löschen einer Zeile auf jeder Datenseite nicht zwangsläufig gesenkt.
Zugeordneter Datenspeicherplatz Die Menge an formatiertem Dateispeicherplatz zum Speichern von Daten. Die Menge des zugeordneten Speicherplatzes wächst automatisch an, wird aber nach dem Löschen nicht kleiner. Dieses Verhalten stellt sicher, dass Daten später schneller eingefügt werden, da der Platz nicht neu formatiert werden muss.
Zugeordneter Datenspeicherplatz (ungenutzt) Die Differenz zwischen der Menge des zugeordneten Datenspeicherplatzes und des genutzten Datenspeicherplatzes. Diese Menge ist die maximale Menge des freien Speicherplatzes, die freigegeben werden kann, indem Datendateien von Datenbanken verkleinert werden.
Maximale Datengröße Die maximale Speicherplatzmenge, die zum Speichern von Datenbankdaten verwendet werden kann. Die Menge des zugeordneten Datenspeicherplatzes kann die maximale Datengröße nicht überschreiten.

Das folgende Diagramm veranschaulicht die Beziehung zwischen den verschiedenen Arten von Speicherplatz für eine Datenbank.

Diagram that demonstrates the size of difference database space concepts in the database quantity table.

Abfrage einer einzelnen Datenbank nach Dateispeicherplatzinformationen

Verwenden Sie die folgende Abfrage, um die Menge des Datenbank-Datenspeicherplatzes und die Menge des zugeordneten ungenutzten Speicherplatzes zurückzugeben. Als Einheit für das Abfrageergebnis wird MB verwendet.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Überwachen der Belegung des Protokollspeicherplatzes

Überwachen Sie mithilfe von sys.dm_db_log_space_usage die Belegung des Protokollspeicherplatzes. Diese DMV gibt Informationen zum derzeit belegten Protokollspeicherplatz zurück und zeigt an, wann das Transaktionsprotokoll abgeschnitten werden muss.

Informationen zur aktuellen Größe einer Protokolldatei, ihrer maximalen Größe sowie der für die Datei festgelegten automatischen Vergrößerungsoption können Sie auch die size-, max_size-, and growth-Spalten für die betreffende Protokolldatei in sys.database_files entnehmen.

Wichtig

Achten Sie darauf, den Protokolldatenträger nicht zu überlasten. Stellen Sie sicher, dass der Protokollspeicher den IOPS-Anforderungen und Anforderungen an niedrige Latenzen für Ihre Transaktionslast gerecht wird.

Verkleinern der Protokolldatei

Um die physische Größe einer physischen Protokolldatei zu verringern, indem sie freien Speicherplatz in der Datei an das Betriebssystem zurückgibt, verkleinern Sie die Protokolldatei. Eine Verkleinerung macht nur einen Unterschied, wenn eine Transaktionsprotokolldatei nicht genutzten Speicherplatz enthält.

Wenn die Protokolldatei voll ist, wahrscheinlich wegen geöffneter Transaktionen, untersuchen Sie, was die Transaktionsprotokoll-Kürzung verhindert.

Achtung

Die Verkleinerungsvorgänge sollten nicht als ein regulärer Wartungsvorgang betrachtet werden. Die Daten- und Protokolldateien, die aufgrund regelmäßiger, wiederkehrender Geschäftsvorgänge zunehmen, erfordern keine Verkleinerungsvorgänge. Verkleinerungsbefehle können während der Ausführung der Datenbank zu einer Beeinträchtigung der Leistung führen und sollten daher nur zu Zeiten mit geringer Auslastung ausgeführt werden. Es ist nicht empfehlenswert, Datendateien zu verkleinern, wenn die regelmäßige Arbeitslast der Anwendung dazu führt, dass die Dateien wieder auf die gleiche Größe anwachsen.

Beachten Sie die möglichen negativen Auswirkungen des Verkleinerns von Datenbankdateien auf die Leistung, siehe Indexpflege nach dem Verkleinern.

Berücksichtigen Sie vor dem Verkleinern des Transaktionsprotokolls die Faktoren, die die Protokollkürzung verzögern können. Wenn der Speicherplatz nach einer Protokollverkleinerung wieder benötigt wird, vergrößert sich das Transaktionsprotokoll wieder und führt bei der Protokollvergrößerung infolgedessen zu einem Leistungsoverhead. Weitere Informationen finden Sie unter Empfehlungen.

Sie können eine Protokolldatei nur dann verkleinern, wenn die Datenbank online und mindestens eine virtuelle Protokolldatei (Virtual Log File, VLF) verfügbar ist. In einigen Fällen ist eine Verkleinerung des Protokolls möglicherweise erst nach der nächsten Protokollkürzung möglich.

Faktoren, wie z.B. Transaktionen mit langer Laufzeit, können VLFs über einen längeren Zeitraum hinweg aktiv halten, können die Protokollverkleinerung einschränken oder sogar gänzlich verhindern. Weitere Informationen finden Sie unter Faktoren, die die Protokollkürzung verzögern können.

Beim Verkleinern einer Protokolldatei werden VLFs entfernt, die keinen Teil des logischen Protokolls enthalten (d.h. inaktive VLFs). Beim Verkleinern einer Transaktionsprotokolldatei werden inaktive VLFs vom Ende der Protokolldatei entfernt, um das Protokoll in etwa auf die Zielgröße zu verkleinern.

Weitere Informationen über Verkleinerungsvorgänge finden Sie unter den folgenden Links:

Verkleinern einer Protokolldatei (ohne die Datenbankdateien zu verkleinern)

Überwachen der Protokollverkleinerungsereignisse

Überwachen von Protokollspeicherplatz

Indexpflege nach dem Schrumpfen

Nachdem ein Verkleinerungsvorgang für Datendateien abgeschlossen wurde, können Indizes fragmentiert werden. Dies verringert die Wirksamkeit der Leistungsoptimierung für bestimmte Arbeitslasten, z. B. für Abfragen mit großen Suchvorgängen. Wenn nach dem Abschluss des Verkleinerungsvorgang eine Leistungsbeeinträchtigung auftritt, sollten Sie eine Indexwartung in Betracht ziehen, um die Indizes neu zu erstellen. Denken Sie daran, dass der Neuaufbau von Indizes freien Speicherplatz in der Datenbank erfordert und daher zu einer Vergrößerung des zugewiesenen Speicherplatzes führen kann, was dem Effekt der Verkleinerung entgegenwirkt.

Weitere Informationen zur Indexpflege finden Sie unter Optimierung der Indexpflege zur Verbesserung der Abfrageleistung und Reduzierung des Ressourcenverbrauchs.

Hinzufügen oder Vergrößern einer Protokolldatei

Sie können Speicherplatz schaffen, indem Sie entweder die vorhandene Protokolldatei vergrößern (sofern der Speicherplatz dies zulässt) oder der Datenbank eine neue Protokolldatei hinzufügen, wofür normalerweise ein anderer Datenträger verwendet wird. Eine Transaktionsprotokolldatei ist ausreichend, es sei denn, der Protokollspeicherplatz und der Speicherplatz auf dem Volume, auf dem die Protokolldatei gespeichert ist, sind ausgeschöpft.

  • Sie können der Datenbank eine Protokolldatei hinzufügen, indem Sie die ADD LOG FILE-Klausel der ALTER DATABASE-Anweisung verwenden. Durch das Hinzufügen einer Protokolldatei kann das Protokoll vergrößert werden.
  • Um die Protokolldatei zu vergrößern, verwenden Sie die MODIFY FILE-Klausel der ALTER DATABASE-Anweisung unter Angabe der Syntax SIZE und MAXSIZE. Weitere Informationen finden Sie unter ALTER DATABASE-Optionen für Dateien und Dateigruppen (Transact-SQL).

Weitere Informationen finden Sie unter Empfehlungen.

Optimieren der Größe des tempdb-Transaktionsprotokolls

Beim Neustarten einer Serverinstanz wird das Transaktionsprotokoll der tempdb-Datenbank auf seine ursprüngliche Größe (vor einer automatischen Größenerweiterung) zurückgesetzt. Dies kann eine Leistungsminderung des tempdb-Transaktionsprotokolls zur Folge haben.

Der damit verbundene Verwaltungsaufwand lässt sich vermeiden, indem Sie nach dem Starten oder erneuten Starten der Serverinstanz die Größe des tempdb-Transaktionsprotokolls erhöhen. Weitere Informationen finden Sie unter tempdb Database.

Steuern einer Transaktionsprotokolldatei

Sie können die Anweisung ALTER DATABASE-Optionen für Dateien und Dateigruppen (Transact-SQL) verwenden, um die Vergrößerung einer Transaktionsprotokolldatei zu steuern. Beachten Sie Folgendes:

  • Verwenden Sie die Option SIZE, um die aktuelle Dateigröße in KB-, MB-, GB- und TB-Einheiten zu ändern.
  • Verwenden Sie die Option FILEGROWTH, um das Vergrößerungsinkrement zu ändern. Der Wert 0 gibt an, dass die automatische Vergrößerung deaktiviert und kein zusätzlicher Speicherplatz zulässig ist.
  • Verwenden Sie die Option MAXSIZE, um die maximale Größe einer Protokolldatei in KB-, MB-, GB- und TB-Einheiten zu steuern oder die Vergrößerung auf UNLIMITED festzulegen.

Weitere Informationen finden Sie unter Empfehlungen.

Empfehlungen

Es folgen einige allgemeine Empfehlungen für die Arbeit mit Transaktionsprotokolldateien:

  • Das mit der Option FILEGROWTH festgelegte automatische Vergrößerungsinkrement (autogrow) des Transaktionsprotokolls muss groß genug sein, um den Anforderungen der Workloadtransaktionen immer einen Schritt voraus sein. Die Schrittweite für die Dateivergrößerung sollte für eine Protokolldatei stets groß genug sein, um häufige Erweiterungen zu vermeiden. Ein guter Anhaltspunkt für die korrekte Anpassung der Größe eines Transaktionsprotokolls ist die Überwachung der Menge der Protokolldaten, die in folgenden Zeiträumen belegt werden:

    • Die Zeit, die erforderlich ist, um eine vollständige Sicherung durchzuführen, da Protokollsicherungen erst nach Beendigung der vollständigen Sicherung durchgeführt werden können
    • Die Zeit, die für die umfangreichsten Vorgänge zur Indexwartung erforderlich ist
    • Die Zeit, die für die Ausführung des größten Batches in einer Datenbank erforderlich ist

  • Wenn Sie die Option FILEGROWTHautogrow für Daten- und Protokolldateien festlegen, kann es empfehlenswert sein, diese anstelle von percentage in size festzulegen, um eine bessere Steuerung der Wachstumsrate zu ermöglichen, da der Prozentsatz eine stetig steigende Zahl darstellt.

    • In Versionen vor SQL Server 2022 (16.x) kann für Transaktionsprotokolle nicht die schnelle Dateiinitialisierung verwendet werden, so dass verlängerte Protokollwachstumszeiten besonders kritisch sind.

    • Ab SQL Server 2022 (16.x) (alle Editionen) und in Azure SQL-Datenbank kann die sofortige Dateiinitialisierung jedoch Vorteile bei Ereignissen durch die Zunahme von Transaktionsprotokollen auf bis zu 64 MB bieten. Der Standardschritt bei der automatischen Vergrößerung für neue Datenbanken beträgt 64 MB. Ereignisse bei einer automatischen Zunahme bei Transaktionsprotokolldateien von mehr als 64 MB profitieren nicht von der sofortigen Dateiinitialisierung.

    • Als bewährte Methode sollten Sie für Transaktionsprotokolle den Wert der Option FILEGROWTH nicht auf über 1.024 MB festlegen. Die Standardwerte für die Option FILEGROWTH lauten wie folgt:

      Version Standardwerte
      Seit SQL Server 2016 (13.x) Daten: 64 MB, Protokolldateien: 64 MB
      Seit SQL Server 2005 (9.x) Daten: 1 MB, Protokolldateien: 10 %
      Vor SQL Server 2005 (9.x) Daten: 10 %, Protokolldateien: 10 %

  • Ein kleines automatisches Wachstumsinkrement könnte dazu führen, dass zu viele kleine VLFs generiert werden und die Leistung beeinträchtigt wird. Informationen darüber, wie Sie die optimale VLF-Verteilung für die aktuelle Größe des Transaktionsprotokolls aller Datenbanken in einer bestimmten Instanz sowie die benötigten Wachstumsinkremente zum Erreichen der erforderlichen Größe ermitteln, finden Sie in diesem Skript zur Analyse und Behebung von VLFs, bereitgestellt vom SQL Tiger Team.

  • Ein großes automatisches Wachstumsinkrement kann zwei Probleme verursachen:

    • Ein großes automatisches Wachstumsinkrement kann dazu führen, dass die Datenbank angehalten wird, während der neue Speicherplatz zugewiesen wird, was zu Abfragetimeouts führen kann.
    • Ein großes automatisches Wachstumsinkrement könnte dazu führen, dass zu wenige und große VLFs generiert werden, und darüber hinaus die Leistung beeinträchtigen. Informationen darüber, wie Sie die optimale VLF-Verteilung für die aktuelle Größe des Transaktionsprotokolls aller Datenbanken in einer bestimmten Instanz sowie die benötigten Wachstumsinkremente zum Erreichen der erforderlichen Größe ermitteln, finden Sie in diesem Skript zur Analyse und Behebung von VLFs, bereitgestellt vom SQL Tiger Team.

  • Auch bei aktiviertem autogrow-Inkrement können Sie eine Meldung erhalten, dass das Transaktionsprotokoll voll ist, wenn es nicht schnell genug wachsen kann, um die Anforderungen Ihrer Abfrage zu erfüllen. Weitere Informationen zum Ändern des Wachstumsinkrements finden Sie unter ALTER DATABASE-Optionen für Dateien und Dateigruppen (Transact-SQL).

  • Durch die Verwaltung von mehreren Protokolldateien in einer Datenbank wird die Leistung in keiner Weise verbessert, da die Transaktionsprotokolldateien nicht wie Datendateien eine proportionale Füllung in derselben Dateigruppe durchführen.

  • Für Protokolldateien kann eine automatische Verkleinerung durchgeführt werden. Dies wird jedoch nicht empfohlen, und die Datenbankeigenschaft auto_shrink ist standardmäßig auf FALSE festgelegt. Wenn auto_shrink auf TRUE festgelegt ist, wird die Größe einer Datei nur dann automatisch verkleinert, wenn mehr als 25 Prozent des Speicherplatzes ungenutzt sind.

Nächste Schritte