☰

Wie kann die Auslastung des SQL Server-Protokollspeichers zu über 100 Prozent liegen?

tag-icon • tag-icon ms-sql
Wie kann die Auslastung des SQL Server-Protokollspeichers zu über 100 Prozent liegen?

Wir sehen Anwendungsfehler (Ausnahmen), die das Transaktionsprotokoll melden "ist voll wegen ACTIVE_TRANSACTION". Es wurden keine ausstehenden Transaktionen von gemeldet DBCC OPENTRAN.

Beim Ausführen DBCC SQLPERF('logspace')sehe ich, dass die Protokollgröße nur 1,3 MB beträgt, aber der verwendete Protokollspeicherplatz beträgt 107,7 %.

Diese Datenbank ist mit einer maximalen Protokolldateigröße von über 2 TB konfiguriert, die Anfangsgröße beträgt 2 MB, wobei „Autogrowth“ auf 10 % eingestellt ist. Das Wiederherstellungsmodell ist auf „Einfach“ eingestellt.

Wie kann der genutzte Protokollspeicherplatz über 100 % betragen und warum wird bei so viel verfügbarem Speicherplatz eine Ausnahme generiert?

Antwort1

Suchen Sie nach dem/den Täter(n)

Du könntest rennensp_WhoIsActiveund sehen Sie, wer was ausführt, und sehen Sie sich die Logik dieser laufenden Prozesse/aktiven Transaktionen an. Prüfen Sie, ob T-SQL-Abfragen optimiert werden können, um schneller oder als kleinere Transaktionen ausgeführt zu werden, sodass der ungenutzte freie Speicherplatz in der Protokolldatei schneller zur Wiederverwendung freigegeben wird.

Es ist durch Design möglich

Einfaches Wiederherstellungsmodell für SQL Server

Jede Transaktion wird weiterhin in das Transaktionsprotokoll geschrieben, aber sobald die Transaktion abgeschlossen ist und die Daten in die Datendatei geschrieben wurden, kann der in der Transaktionsprotokolldatei verwendete Speicherplatz nun für neue Transaktionen wiederverwendet werden.

Quelle

Ich habe Fälle gesehen, in denen eine lang andauernde Transaktion das Transaktionsprotokoll in einem SIMPLEWiederherstellungsmodell-Setup enorm vergrößert hat. Diese Transaktion ist tatsächlich fehlgeschlagen und es hat genauso lange gedauert, bis sie zurückgesetzt wurde. Dieses Problem kann also durch lang andauernde Transaktionen, schlechte Leistung oder schlecht geschriebene, nicht optimierte Abfragen verursacht werden.

Sobald Speicherplatz für die SIMPLETransaktionsprotokolldatei einer Wiederherstellungsmodelldatenbank zugewiesen wurde,ungenutzter freier Speicherplatz im Transaktionsprotokolloderpro automatischer Vergrößerung des freien Speicherplatzes auf Betriebssystemebene, die Transaktionsprotokolldatei behält den neuen Speicherplatz bei, bis beispielsweise eine Dateiverkleinerungsoperation erfolgt DBCC SHRINKFILE (database_log, 2048).

Wichtig:Wenn beispielsweise eine Dateiverkleinerungsoperation stattfindet DBCC SHRINKFILE (database_log, 2048), wird nur der ungenutzte Protokollspeicherplatz im Transaktionsprotokoll als freier Speicherplatz an das Betriebssystem zurückgegeben. Aktive Transaktionen, die in das Transaktionsprotokoll geschrieben werden, werden während einer Dateiverkleinerungsoperation nicht freigegeben.

Verkleinern der Protokolldatei

Das Problem beim Verkleinern der Protokolldatei besteht darin, dass die Protokolldatei beim nächsten Ausführen einer großen Transaktion oder einer schlecht geschriebenen Abfrage erneut voll wird und Sie den Verkleinerungsvorgang wiederholen müssen. Suchen und beheben Sie das Grundproblem, um dieses Problem dauerhaft zu beheben. Fahren Sie in der Zwischenzeit mit dem Verkleinern der Protokolldatei fort.

Beheben Sie die Grundursache

Das Grundproblem ist wahrscheinlich eine Abfrage. Wenn Sie also ermitteln, wer was tut, Kontakt zu diesem Mitarbeiter aufnehmen und ihm das Problem mit Ihren Ergebnissen melden, setzen Sie ihn unter Druck, seine Logik zu korrigieren, um die Festplattenpartitionen des Servers nicht zu überlasten. Versuchen Sie, die Logik zu optimieren, um die Leistung der Abfrage zu optimieren.

  • Der Grundursache auf der Spur

    Manchmal liegt die Ursache nicht in einer lang laufenden Transaktion – es könnte zum Beispiel sein, dass jemand die Replikation eingerichtet und nie wieder ordnungsgemäß beendet hat. Überprüfen Sie zunächstlog_reuse_wait_desc in sys.databases:

    SELECT name, log_reuse_wait_desc FROM sys.databases;

    Bedenken Sie jedoch, dass dies nur eine Momentaufnahme ist, warum das Protokoll JETZT nicht verkleinert werden kann.

    Wenn Sie dort nichts Interessantes finden, können Siesp_WhoIsActive in eine Tabelle protokollieren um zu erkennen, wenn Leute eine BEGIN TRANSitzung ausführen und diese stundenlang offen lassen. Suchen Sie nach Transaktionen mit langer Laufzeit, sprechen Sie mit dem Eigentümer und fragen Sie, ob er seine Arbeit in kleineren Abschnitten statt in einer einzigen großen Transaktion erledigen kann.

  • Metadaten des Protokolldateispeicherplatzes

    DBCC SQLPERF(logspace)ist ein absolut funktionaler Befehl, wenn Sie nur an der Nutzung Ihrer Datenbankprotokolldateien interessiert sind. Er liefert die kumulierte Größe jeder Protokolldatei für jede Datenbank auf der SQL Server-Instanz sowie die Menge des verbrauchten Speicherplatzes (als Prozentsatz der Gesamtgröße der Protokolldatei). Nachteilig ist, dass die Ergebnisse ein Aggregat für die Datenbank darstellen. Wenn Sie über mehrere Protokolldateien verfügen, werden die Ergebnisse auf Datenbankebene und nicht auf Dateiebene angezeigt.

    Dieser DBCC-Befehl ist zwar praktisch, wenn Sie Probleme prüfen, die sich aus einer unzureichenden Planung der Protokollsicherung oder einer falschen Größe der Protokolldateien ergeben, er liefert Ihnen jedoch nicht alle Informationen, die Sie benötigen, um fundierte Entscheidungen hinsichtlich der Größe Ihrer Protokolldateien und der Anpassung der Häufigkeit Ihres Sicherungsplans oder Ihres Wiederherstellungsmodells zu treffen.

    Quelle

verwandte Informationen