Fehler 9002: Das Transaktionsprotokoll für die Datenbank ist aufgrund AVAILABILITY_REPLICA Fehlermeldung in SQL Server voll

  • Artikel

In diesem Artikel erfahren Sie, wie Sie den Fehler 9002 beheben, der auftritt, wenn das Transaktionsprotokoll in SQL Server groß wird oder nicht mehr verfügbar ist.

Ursprüngliche Produktversion: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012
Ursprüngliche KB-Nummer: 2922898

Problembeschreibung

Stellen Sie sich folgendes Szenario vor:

  • Sie haben Microsoft SQL Server 2012 oder eine höhere Version auf einem Server installiert.
  • Das instance von SQL Server ist ein primäres Replikat in Always On Verfügbarkeitsgruppenumgebung.
  • Die Option für die automatische Vergrößerung für Transaktionsprotokolldateien wird in SQL Server festgelegt.

In diesem Szenario kann das Transaktionsprotokoll groß werden und der Speicherplatz nicht mehr verfügbar sein oder die für das Transaktionsprotokoll auf dem primären Replikat festgelegte MaxSize-Option überschreiten, und Sie erhalten eine Fehlermeldung, die der folgenden ähnelt:

Fehler: 9002, Schweregrad: 17, Status: 9. Das Transaktionsprotokoll für die '%.*ls'-Datenbank ist aufgrund von 'AVAILABILITY_REPLICA' voll.

Ursache

Dies tritt auf, wenn die protokollierten Änderungen am primären Replikat noch nicht auf dem sekundären Replikat gehärtet sind. Weitere Informationen zum Datensynchronisierungsprozess in Always On-Umgebung finden Sie unter Datensynchronisierungsprozess.

Problembehandlung

Es gibt zwei Szenarien, die zu einer Protokollvergrößerung in einer Verfügbarkeitsdatenbank 'AVAILABILITY_REPLICA' log_reuse_wait_descführen können:

  • Szenario 1: Latenz bei der Übermittlung protokollierter Änderungen an der sekundären Datenbank

    Wenn Transaktionen Daten im primären Replikat ändern, werden diese Änderungen in Protokolldatensatzblöcken gekapselt, und diese protokollierten Blöcke werden an die Datenbankprotokolldatei am sekundären Replikat übermittelt und gehärtet. Das primäre Replikat kann Protokollblöcke in seiner eigenen Protokolldatei erst überschreiben, wenn diese Protokollblöcke übermittelt und in der entsprechenden Datenbankprotokolldatei in allen sekundären Replikaten gehärtet wurden. Jede Verzögerung bei der Übermittlung oder Härtung dieser Blöcke an ein Replikat in der Verfügbarkeitsgruppe verhindert das Abschneiden dieser protokollierten Änderungen in der Datenbank auf dem primären Replikat und führt zu einer Zunahme der Protokolldateinutzung.

    Weitere Informationen finden Sie unter Hohe Netzwerklatenz oder geringer Netzwerkdurchsatz verursacht Protokollerstellung auf dem primären Replikat.

  • Szenario 2: Wiederholungswartezeit

    Nach dem Härten in der protokolldatei der sekundären Datenbank wendet ein dedizierter Wiederholungsthread im sekundären Replikat instance die enthaltenen Protokolldatensätze auf die entsprechenden Datendateien an. Das primäre Replikat kann Protokollblöcke erst in seiner eigenen Protokolldatei überschreiben, wenn alle Wiederholungsthreads in allen sekundären Replikaten die enthaltenen Protokolldatensätze angewendet haben.

    Wenn der Wiederholungsvorgang auf einem sekundären Replikat nicht mit der Geschwindigkeit Schritt halten kann, mit der Protokollblöcke auf diesem sekundären Replikat gehärtet werden, führt dies zu einer Protokollvergrößerung am primären Replikat. Das primäre Replikat kann sein eigenes Transaktionsprotokoll nur abschneiden und wiederverwenden, bis alle Wiederholungsthreads des sekundären Replikats angewendet wurden. Wenn mehr als ein sekundäres Element vorhanden ist, vergleichen Sie die truncation_lsn Spalte der sys.dm_hadr_database_replica_states dynamischen Verwaltungssicht für mehrere sekundäre Datenbanken, um zu ermitteln, welche sekundäre Datenbank die Protokollkürzung am meisten verzögert.

    Sie können das Always On-Dashboard und sys.dm_hadr_database_replica_states dynamische Verwaltungsansichten verwenden, um die Protokollsende- und Wiederholungswarteschlange zu überwachen. Einige Schlüsselfelder sind:

    Feld Beschreibung
    log_send_queue_size Menge der Protokolldatensätze, die nicht beim sekundären Replikat eingetroffen sind
    log_send_rate Rate, mit der Protokolldatensätze an die sekundären Datenbanken gesendet werden.
    redo_queue_size Die Menge der Protokolldatensätze in den Protokolldateien des sekundären Replikats, die noch nicht wiederholt wurde, in Kilobyte (KB).
    redo_rate Die Rate, mit der die Protokolldatensätze in einer bestimmten sekundären Datenbank in Kilobyte (KB)/Sekunde neu erstellt werden.
    last_redone_lsn Die tatsächliche Protokollsequenznummer des letzten Protokolldatensatzes, der für die sekundäre Datenbank neu erstellt wurde. last_redone_lsn ist immer kleiner als last_hardened_lsn.
    last_received_lsn Die Protokollblock-ID, die den Punkt angibt, bis zu dem alle Protokollblöcke vom sekundären Replikat empfangen wurden, das diese sekundäre Datenbank hostet. Gibt eine Mit Nullen aufgefüllte Protokollblock-ID an. Es handelt sich nicht um eine tatsächliche Protokollsequenznummer.

    Führen Sie beispielsweise die folgende Abfrage für das primäre Replikat aus, um das Replikat mit dem frühesten truncation_lsn und der Obergrenze zu melden, die das primäre Replikat in seinem eigenen Transaktionsprotokoll freigeben kann:

    SELECT ag.name AS [availability_group_name]
, d.name AS [database_name]
, ar.replica_server_name AS [replica_instance_name]
, drs.truncation_lsn , drs.log_send_queue_size
, drs.redo_queue_size
FROM sys.availability_groups ag
INNER JOIN sys.availability_replicas ar
    ON ar.group_id = ag.group_id
INNER JOIN sys.dm_hadr_database_replica_states drs
    ON drs.replica_id = ar.replica_id
INNER JOIN sys.databases d
    ON d.database_id = drs.database_id
WHERE drs.is_local=0
ORDER BY ag.name ASC, d.name ASC, drs.truncation_lsn ASC, ar.replica_server_name ASC

    Korrekturmaßnahmen können Folgendes umfassen, sind aber nicht auf folgendes beschränkt:

    • Stellen Sie sicher, dass es bei der sekundären Instanz keinen Ressourcen- oder Leistungsengpass gibt.
    • Stellen Sie sicher, dass der Wiederholungsthread nicht auf der sekundären Ebene blockiert ist. Verwenden Sie das lock_redo_blocked erweiterte Ereignis, um zu ermitteln, wann dies geschieht und für welche Objekte der Wiederholungsthread blockiert ist.

Problemumgehung

Nachdem Sie die sekundäre Datenbank identifiziert haben, die dies bewirkt, versuchen Sie eine oder mehrere der folgenden Methoden, um dieses Problem vorübergehend zu umgehen:

  • Entfernen Sie die Datenbank aus der Verfügbarkeitsgruppe für die betreffende sekundäre Instanz.

    Hinweis

    Diese Methode führt zum Verlust des Hochverfügbarkeits-/Notfallwiederherstellungsszenarios für die sekundäre Datenbank. Möglicherweise müssen Sie die Verfügbarkeitsgruppe in Zukunft erneut einrichten.

  • Wenn der Wiederholungsthread häufig blockiert wird, deaktivieren Sie das Readable Secondary Feature, indem Sie den ALLOW_CONNECTIONS Parameter des SECONDARY_ROLE für das Replikat in NO ändern.

    Hinweis

    Dadurch wird verhindert, dass Benutzer die Daten im sekundären Replikat lesen, was die Grundursache der Blockierung ist. Sobald die Wiederholungswarteschlange eine akzeptable Größe erreicht hat, sollten Sie das Feature erneut aktivieren.

  • Aktivieren Sie die Einstellung für die automatische Vergrößerung , wenn sie deaktiviert ist und verfügbarer Speicherplatz verfügbar ist.

  • Erhöhen Sie den MaxSize-Wert für die Transaktionsprotokolldatei, wenn sie erreicht wurde und verfügbarer Speicherplatz verfügbar ist.

  • Fügen Sie eine zusätzliche Transaktionsprotokolldatei hinzu, wenn die aktuelle das Systemmaximum von 2 TB erreicht hat oder wenn zusätzlicher Speicherplatz auf einem anderen verfügbaren Volume verfügbar ist.

Weitere Informationen

Gilt für

  • SQL Server 2012 Enterprise
  • SQL Server 2014 Enterprise
  • SQL Server 2014 Business Intelligence
  • SQL Server 2014 Standard
  • SQL Server 2016 Enterprise
  • SQL Server 2016 Standard
  • SQL Server 2017 Enterprise
  • SQL Server 2017 Standard Windows