Freigeben über

Verwalten der Größe der Transaktionsprotokolldatei

Gilt für:SQL Server

In diesem Artikel wird beschrieben, wie Sie die Größe des SQL Server-Transaktionsprotokolls überwachen, das Transaktionsprotokoll verkleinern, eine Transaktionsprotokolldatei hinzufügen oder vergrößern, die tempdb Wachstumsrate des Transaktionsprotokolls optimieren und das Wachstum einer Transaktionsprotokolldatei steuern.

Dieser Artikel bezieht sich auf SQL Server. Obwohl der Prozess ähnlich ist, finden Sie informationen zur Verwaltung von Speicherplatz in Azure SQL unter:

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. Im Allgemeinen erhöht sich der verwendete Platz bei Einfügungen und verringert die Löschungen. 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 Beziehungen zwischen den verschiedenen Arten von Speicherplatz für eine Datenbank.

Diagramm, das die Beziehungen zwischen den verschiedenen Arten von Speicherplatz für eine Datenbank 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 für die automatische Vergrößerung 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 einer 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 aufgrund geöffneter Transaktionen, untersuchen Sie , was das Abschneiden des Transaktionsprotokolls verhindert.

Achtung

Verkleinerungsvorgänge sollten nicht als regulärer Wartungsvorgang betrachtet werden. Daten- und Protokolldateien, die aufgrund regelmäßiger wiederkehrender Geschäftsvorgänge wachsen, 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 wird nicht empfohlen, Datendateien zu verkleinern, wenn eine normale Anwendungsarbeitsauslastung dazu führt, dass die Dateien wieder auf dieselbe zugewiesene Größe anwachsen.

Beachten Sie die potenziellen negativen Auswirkungen auf die Leistung der Verkleinerung von Datenbankdateien. Siehe Indexwartung nach verkleinern.

Bedenken Sie vor dem Verkleinern des Transaktionsprotokolls Faktoren, die das Abschneiden von Protokollen 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 verkleinern, während die Datenbank online ist und mindestens eine virtuelle Protokolldatei (VLF) kostenlos ist. In einigen Fällen ist das Verkleinern des Protokolls nur nach dem nächsten Protokollabschneiden möglich.

Einige Faktoren, z. B. eine lange ausgeführte Transaktion, können VLFs für einen längeren Zeitraum aktiv halten, die Log-Verkleinerung einschränken oder sogar verhindern, dass das Protokoll überhaupt verkleinern kann. Weitere Informationen finden Sie in 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 zu Verkleinerungsvorgängen erfahren Sie in den folgenden Ressourcen:

Verkleinern einer Protokolldatei (ohne die Datenbankdateien zu verkleinern)

Überwachen der Protokollverkleinerungsereignisse

Überwachen von Protokollspeicherplatz

Indexwartung nach einem Verkleinerungsvorgang

Indizes werden möglicherweise fragmentiert, nachdem ein Verkleinerungsvorgang für Datendateien abgeschlossen wurde. Diese Fragmentierung reduziert ihre Effektivität für die Leistungsoptimierung für bestimmte Workloads, z. B. Abfragen, die große Scans verwenden. Wenn nach dem Abschluss des Verkleinerungsvorgang eine Leistungsbeeinträchtigung auftritt, sollten Sie eine Indexwartung in Betracht ziehen, um die Indizes neu zu erstellen. Beachten Sie, dass Indexneuerstellungen freien Speicherplatz in der Datenbank erfordern und somit den zugewiesenen Speicherplatz erhöhen können, was der Auswirkung des Verkleinerungsvorgangs entgegenwirkt.

Weitere Informationen finden Sie unter Optimieren der Indexwartung, um die Abfrageleistung zu verbessern und den Ressourcenverbrauch zu verringern.

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. Mit dieser Aktion 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 (Transact-SQL) Datei- und Dateigruppenoptionen.

Weitere Informationen finden Sie unter "Empfehlungen".

Optimieren der Größe des tempdb-Transaktionsprotokolls

Durch den Neustart einer Serverinstanz wird die Größe des Transaktionsprotokolls der Datenbank auf die ursprüngliche Größe der tempdb automatischen Vergrößerung geändert. Diese Größenänderung kann die Leistung des tempdb Transaktionsprotokolls verringern.

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 in der Tempdb-Datenbank.

Steuern einer Transaktionsprotokolldatei

Verwenden Sie die ALTER DATABASE (Transact-SQL) -Datei- und Dateigruppenoptionen-Anweisung , um das Wachstum einer Transaktionsprotokolldatei zu verwalten. Beachten Sie Folgendes:

  • Verwenden Sie die SIZE Option, 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 MAXSIZE Option, um die maximale Größe einer Protokolldatei in KB-, MB-, GB- und TB-Einheiten zu steuern oder um das Wachstum auf festzulegen UNLIMITED.

Weitere Informationen finden Sie unter "Empfehlungen".

Empfehlungen

Im Folgenden finden Sie einige allgemeine Empfehlungen, die Sie berücksichtigen sollten, wenn Sie mit Transaktionsprotokolldateien arbeiten:

  • Das automatische Wachstum (autogrowth) 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 Tipp für die ordnungsgemäße Größenanpassung eines Transaktionsprotokolls besteht darin, die Menge der während des Vorgangs belegten Protokolle zu überwachen:

    • Die zum Ausführen einer vollständigen Sicherung erforderliche Zeit, da Protokollsicherungen erst auftreten können, wenn sie abgeschlossen sind.
    • Die Zeit, die für die umfangreichsten Vorgänge zur Indexwartung erforderlich ist
    • Die Zum Ausführen des größten Batches in einer Datenbank erforderliche Zeit.

  • Wenn Sie die automatische Vergrößerung für Daten- und Protokolldateien mithilfe der FILEGROWTH Option festlegen, ist es möglicherweise besser, die Größe anstelle des Prozentsatzes 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 sofortige Dateiinitialisierung 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 kleiner automatischer Zuwachs 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:

    • Die Datenbank kann dazu führen, dass die Datenbank angehalten wird, während der neue Speicherplatz zugewiesen ist, was zu Abfragetimeouts führen kann.
    • Es kann zu wenige und große VLFs generieren und sich auch auf die Leistung auswirken. 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 Wachstumsinkrements finden Sie unter ALTER DATABASE (Transact-SQL) Datei- und Dateigruppenoptionen.

  • 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. Diese Konfiguration wird jedoch nicht empfohlen, und die AUTO_SHRINK Datenbankeigenschaft ist standardmäßig auf FALSE festgelegt. Wenn AUTO_SHRINK auf TRUE festgelegt ist, reduziert die automatische Verkleinerung die Größe einer Datei nur, wenn mehr als 25 Prozent des Speicherplatzes nicht verwendet werden.

Zugehöriger Inhalt

  • Last updated on