Freigeben über

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. Ähnliche Informationen zum Verwalten der Größe von Transaktionsprotokolldateien in Azure SQL verwaltete Instanz finden Sie unter "Verwalten von Dateispeicher für Datenbanken in Azure SQL verwaltete Instanz". 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

Das Verständnis der folgenden Speicherplatzmengen ist wichtig für die Verwaltung des Dateispeichers einer Datenbank.

Datenbankmenge Definition Kommentare
Genutzter Speicherplatz Der zum Speichern von Datenbankdaten verwendete Speicherplatz. In der Regel erhöht (verringert) sich der Platzbedarf bei eingefügten (gelöschten) Daten. In einigen Fällen ändert sich der verwendete Platz nicht bei Einfügungen oder Löschvorgängen, abhängig von der Menge und dem Muster der daten, die an dem Vorgang beteiligt sind, und jeder Fragmentierung. Beispielsweise wird der genutzte Speicherplatz durch Löschen einer Zeile auf jeder Datenseite nicht zwangsläufig gesenkt.
Zugeordneter Datenspeicherplatz Der formatierte Speicherplatz, der zum Speichern von Datenbankdaten zur Verfügung gestellt wurde. Die Menge des zugeordneten Speicherplatzes wächst automatisch an, wird aber nach dem Löschen nicht kleiner. Dieses Verhalten stellt sicher, dass zukünftige Einfügungen schneller sind, da der Platz nicht neu formatiert werden muss.
Zugeordneter Datenspeicherplatz (ungenutzt) Der Unterschied zwischen dem zugewiesenen Betrag und dem verwendeten Datenraum. Diese Menge stellt den maximalen freien Speicherplatz dar, der beim Verkleinern von Datenbankdatendateien zurückzugeben ist.
Maximale Datengröße Der maximale Speicherplatz zum Speichern von Datenbankdaten. Der Umfang des zugeordneten Datenspeicherplatzes kann die maximale Größe für Daten nicht überschreiten.

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

Diagramm, das die Größe der Differenzen von Datenbankraumkonzepten in der Datenbankmengestabelle veranschaulicht.

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 Protokolldatei, zur maximalen Größe und zur Option zum automatischen Vergrößern für die Datei können Sie auch die sizeSpalten max_sizeund growth Spalten für diese Protokolldatei in sys.database_files verwenden.

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

Verkleinern Sie die Protokolldatei, um ihre physische Größe zu verringern, indem Sie freien Speicherplatz an das Betriebssystem zurückgeben. Eine Verkleinerung macht nur dann einen Unterschied, wenn eine Transaktionsprotokolldatei ungenutzten Platz enthält.

Wenn die Protokolldatei voll ist, wahrscheinlich wegen offener Transaktionen, untersuchen Sie, was das Abschneiden des Transaktionsprotokolls 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. Verkleinern von Befehlen wirkt sich auf die Datenbankleistung aus, während sie ausgeführt wird; sie sollten in Zeiträumen mit geringer Nutzung 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 potenziellen negativen Auswirkungen der Leistungseinbußen bei der Verkleinerung von Datenbankdateien; siehe Indexwartung nach verkleinern.

Berücksichtigen Sie vor dem Verkleinern des Transaktionsprotokolls die Faktoren, die die Protokollkürzung verzögern können. Wenn Speicherplatz nach dem Verkleinern des Protokolls erneut benötigt wird, wird das Transaktionsprotokoll erneut vergrößert, was den Leistungsaufwand bei Protokollwachstumsvorgängen einführt. 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 das Verkleinern des Protokolls nur nach dem nächsten Protokollabschneiden möglich.

Faktoren, wie z.B. Transaktionen mit langer Laufzeit, können VLFs für einen längeren Zeitraum aktiv halten, 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

Indizes werden möglicherweise fragmentiert, nachdem ein Verkleinerungsvorgang für Datendateien abgeschlossen wurde. Dies reduziert ihre Effektivität für die Leistungsoptimierung für bestimmte Workloads, z. B. Abfragen mit großen Scans. 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 Indexneuerstellungen freien Speicherplatz in der Datenbank erfordern und somit den zugewiesenen Speicherplatz erhöhen können, wodurch der Effekt der Verkleinerung entgegengewirkt wird.

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 gewinnen, indem Sie die vorhandene Protokolldatei (sofern Speicherplatz zulässt) vergrößern oder eine Protokolldatei zur Datenbank hinzufügen, in der Regel auf einem anderen Datenträger. Eine Transaktionsprotokolldatei reicht aus, es sei denn, der Protokollspeicher wird ausgelaufen, und der Speicherplatz wird auch auf dem Volume ausgeführt, das die Protokolldatei enthält.

Sie können der Datenbank eine Protokolldatei hinzufügen, indem Sie die ADD LOG FILE-Klausel der ALTER DATABASE-Anweisung verwenden. Dadurch kann das Protokoll vergrößert werden.

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.

Sie können diesen Aufwand vermeiden, indem Sie die tempdb Transaktionsprotokollgröße nach dem Starten oder Neustart der Serverinstanz 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 "GRÖßE", 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 das automatische Wachstum deaktiviert ist und kein zusätzlicher Platz 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 um das Wachstum auf UNLIMITED festzulegen.

Weitere Informationen finden Sie unter Empfehlungen.

Empfehlungen

Im Folgenden finden Sie einige allgemeine Empfehlungen, wenn Sie mit Transaktionsprotokolldateien arbeiten:

  • Das automatische Wachstum (automatisch vergrößern) des Transaktionsprotokolls muss, wie durch die FILEGROWTH Option festgelegt, groß genug sein, um den Anforderungen der Workloadtransaktionen zu entsprechen. 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 zum Ausführen einer vollständigen Sicherung erforderlich ist, da Protokollsicherungen erst auftreten können, wenn sie abgeschlossen sind.
    • 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 für Daten- und Protokolldateien mithilfe der FILEGROWTH Option automatisch vergrößern, empfiehlt es sich möglicherweise, sie in Größe statt prozentual festzulegen, um eine bessere Kontrolle des Wachstumsverhältnisses zu ermöglichen, da ein Prozentsatz ein immer wachsender Betrag ist.

    • In Versionen vor SQL Server 2022 (16.x) können Transaktionsprotokolle keine Instant File Initialization verwenden, sodass erweiterte Protokollwachstumszeiten besonders wichtig 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. Die standardmäßige Automatische Vergrößerungsgröße 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 den FILEGROWTH Optionswert nicht über 1.024 MB für Transaktionsprotokolle festlegen. Die Standardwerte für die FILEGROWTH Option sind:

      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 Auswachsen kann zu viele kleine VLFs generieren und die Leistung verringern. Um die optimale VLF-Verteilung für die aktuelle Transaktionsprotokollgröße aller Datenbanken in einer bestimmten Instanz und die erforderlichen Wachstumsschritte zu ermitteln, um die erforderliche Größe zu erreichen, lesen Sie dieses Skript zum Analysieren und Beheben von VLFs, die vom SQL Tiger Team bereitgestellt werden.

  • Ein großes automatisches Vergrößerungsinkrement kann zwei Probleme verursachen:

    • Ein großes automatisches Vergrößerungsinkrement kann dazu führen, dass die Datenbank pausiert, während der neue Speicherplatz zugewiesen wird, was möglicherweise zu Zeitüberschreitungen bei Abfragen führt.
      • Ein großes automatisches Vergrößerungsinkrement könnte dazu führen, dass wenige große VLFs generiert werden und darüber hinaus die Leistung beeinträchtigt. Um die optimale VLF-Verteilung für die aktuelle Transaktionsprotokollgröße aller Datenbanken in einer bestimmten Instanz und die erforderlichen Wachstumsschritte zu ermitteln, um die erforderliche Größe zu erreichen, lesen Sie dieses Skript zum Analysieren und Beheben von VLFs, die vom SQL Tiger Team bereitgestellt werden.

  • Selbst wenn die automatische Vergrößerung aktiviert ist, 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 Vergrößerungsinkrements finden Sie unter ALTER DATABASE-Optionen für Dateien und Dateigruppen (Transact-SQL).

  • Wenn mehrere Protokolldateien in einer Datenbank vorhanden sind, wird die Leistung in keiner Weise verbessert, da die Transaktionsprotokolldateien keine proportionale Füllung wie Datendateien in derselben Dateigruppe verwenden.

Für Protokolldateien kann eine automatische Verkleinerung durchgeführt werden. Dies wird jedoch nicht empfohlen, und die auto_shrink Datenbankeigenschaft 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. - Die Datei wird entweder auf die Größe verkrumpft, in der nur 25 Prozent der Datei nicht verwendet werden, oder auf die Originalgröße der Datei, je nachdem, welcher Wert größer ist. – Informationen zum Ändern der Einstellung der auto_shrink-Eigenschaft finden Sie unter Anzeigen oder Ändern der Eigenschaften einer Datenbank und ALTER DATABASE SET-Optionen (Transact-SQL).

Zugehöriger Inhalt