Condividi tramite

Errore 9002: il log delle transazioni per il database è pieno a causa di AVAILABILITY_REPLICA messaggio di errore in SQL Server

  • Articolo

Questo articolo consente di risolvere l'errore 9002 che si verifica quando il log delle transazioni diventa di grandi dimensioni o esaurisce lo spazio in SQL Server.

Versione originale del prodotto: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012
Numero KB originale: 2922898

Sintomi

Prendi in considerazione lo scenario seguente:

  • Microsoft SQL Server 2012 o versione successiva è installato in un server.
  • L'istanza di SQL Server è una replica primaria nell'ambiente Gruppi di disponibilità AlwaysOn.
  • L'opzione di aumento automatico per i file di log delle transazioni è impostata in SQL Server.

In questo scenario, il log delle transazioni può diventare di grandi dimensioni ed esaurire lo spazio su disco o superare l'opzione MaxSize impostata per il log delle transazioni nella replica primaria e viene visualizzato un messaggio di errore simile al seguente:

Errore: 9002, gravità: 17, stato: 9. Il log delle transazioni per il database '%.*ls' è pieno a causa di 'AVAILABILITY_REPLICA'

Causa

Ciò si verifica quando le modifiche registrate nella replica primaria non sono ancora state rafforzate nella replica secondaria. Per altre informazioni sul processo di sincronizzazione dei dati nell'ambiente AlwaysOn, vedere sincronizzazione dati processo di sincronizzazione dei dati.

Risoluzione dei problemi

Esistono due scenari che possono causare la crescita dei log in un database di disponibilità e 'AVAILABILITY_REPLICA' log_reuse_wait_desc:

  • Scenario 1: Latenza che recapita le modifiche registrate al database secondario

    Quando le transazioni modificano i dati nella replica primaria, queste modifiche vengono incapsulate in blocchi di record di log e questi blocchi registrati vengono recapitati e con protezione avanzata al file di log del database nella replica secondaria. La replica primaria non può sovrascrivere blocchi di log nel proprio file di log fino a quando tali blocchi di log non sono stati recapitati e sottoposti a protezione avanzata al file di log del database corrispondente in tutte le repliche secondarie. Qualsiasi ritardo nel recapito o nella protezione avanzata di questi blocchi in qualsiasi replica nel gruppo di disponibilità impedirà il troncamento di tali modifiche registrate nel database nella replica primaria e causerà l'aumento dell'utilizzo del file di log.

    Per altre informazioni, vedere Latenza di rete elevata o bassa velocità effettiva della rete causa la compilazione del log nella replica primaria.

  • Scenario 2: Latenza di rollforward

    Dopo aver applicato la protezione avanzata al file di log del database secondario, un thread di rollforward dedicato nell'istanza di replica secondaria applica i record di log contenuti ai file di dati corrispondenti. La replica primaria non può sovrascrivere i blocchi di log nel proprio file di log fino a quando tutti i thread di rollforward in tutte le repliche secondarie non hanno applicato i record di log contenuti.

    Se l'operazione di rollforward in qualsiasi replica secondaria non è in grado di mantenere il passo con la velocità con cui i blocchi di log vengono rafforzati in tale replica secondaria, la crescita del log verrà eseguita nella replica primaria. La replica primaria può troncare e riutilizzare solo il proprio log delle transazioni fino al punto in cui sono stati applicati tutti i thread di rollforward della replica secondaria. Se sono presenti più database secondari, confrontare la truncation_lsn colonna della sys.dm_hadr_database_replica_states visualizzazione a gestione dinamica tra più database secondari per identificare il database secondario che ritarda il troncamento del log.

    È possibile usare il dashboard Always On e sys.dm_hadr_database_replica_states le viste a gestione dinamica per monitorare la coda di invio dei log e la coda di rollforward. Alcuni campi chiave sono:

    Campo Descrizione
    log_send_queue_size Quantità di record di log non arrivati alla replica secondaria
    log_send_rate Frequenza con cui i record di log vengono inviati ai database secondari.
    redo_queue_size Quantità di record di log nei file di log della replica secondaria che non è ancora stato eseguito il rollforward, in kilobyte (KB).
    redo_rate Frequenza di rollforward dei record di log in un determinato database secondario, in kilobyte (KB)/secondo.
    last_redone_lsn Numero di sequenza del file di log (LSN) effettivo dell'ultimo record di log di cui è stato eseguito il rollforward nel database secondario. last_redone_lsn è sempre minore di last_hardened_lsn.
    last_received_lsn ID blocco di log che identifica il punto a cui sono stati ricevuti tutti i blocchi di log dalla replica secondaria che ospita questo database secondario. Riflette un ID blocco di log riempito con zere. Non è un numero di sequenza di log effettivo.

    Ad esempio, eseguire la query seguente sulla replica primaria per segnalare la replica con il meno recente truncation_lsn ed è il limite superiore che il database primario può recuperare nel proprio log delle transazioni:

    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

    Le misure correttive possono includere, ma non sono limitate alle seguenti:

    • Assicurarsi che non siano presenti colli di bottiglia delle risorse o delle prestazioni nel database secondario.
    • Assicurarsi che il thread Di rollforward non sia bloccato nel database secondario. Usare l'evento lock_redo_blocked esteso per identificare quando si verifica e sugli oggetti bloccati dal thread di rollforward.

Soluzione alternativa

Dopo aver identificato il database secondario che esegue questa operazione, provare uno o più dei metodi seguenti per risolvere temporaneamente questo problema:

  • Disconnessare il database dal gruppo di disponibilità per il database secondario che causa l'errore.

    Note

    Questo metodo comporterà la perdita dello scenario di disponibilità elevata/ripristino di emergenza per il database secondario. Potrebbe essere necessario configurare nuovamente il gruppo di disponibilità in futuro.

  • Se il thread di rollforward viene spesso bloccato, disabilitare la Readable Secondary funzionalità modificando il ALLOW_CONNECTIONS parametro di SECONDARY_ROLE per la replica su NO.

    Note

    Ciò impedirà agli utenti di leggere i dati nella replica secondaria, che è la causa radice del blocco. Dopo che la coda di rollforward è stata eliminata a una dimensione accettabile, è consigliabile abilitare di nuovo la funzionalità.

  • Abilitare l'impostazione di aumento automatico se è disabilitata ed è disponibile spazio su disco.

  • Aumentare il valore MaxSize per il file di log delle transazioni se è stato raggiunto ed è disponibile spazio su disco.

  • Aggiungere un file di log delle transazioni aggiuntivo se quello corrente ha raggiunto il massimo di 2 TB di sistema o se è disponibile spazio aggiuntivo in un altro volume disponibile.

Ulteriori informazioni

Si applica a

  • 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