Share via

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 grande 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

Considerare lo scenario descritto di seguito:

  • Microsoft SQL Server 2012 o versione successiva è installato in un server.
  • L'istanza di SQL Server è una replica primaria nell'ambiente Always On gruppi di disponibilità.
  • 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 grande e 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 protette nella replica secondaria. Per altre informazioni sul processo di sincronizzazione dei dati nell Always On ambito, vedere Processo di sincronizzazione dei dati.

Risoluzione dei problemi

Esistono due scenari che possono causare l'aumento 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 protetti nel file di log del database nella replica secondaria. La replica primaria non può sovrascrivere i 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 delle modifiche registrate nel database nella replica primaria e ne causerà l'aumento dell'utilizzo dei file di log.

    Per altre informazioni, vedere Latenza di rete elevata o velocità effettiva di rete bassa 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 su una replica secondaria non è in grado di tenere il passo con la velocità con cui i blocchi di log vengono protetti in tale replica secondaria, comporterà un aumento dei log nella replica primaria. La replica primaria può solo troncare e riutilizzare 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 maggiormente il troncamento del log.

    È possibile usare il dashboard di 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 stata rifatto, in kilobyte (KB).
    redo_rate Frequenza di rollforward dei record di log in un database secondario specificato, in kilobyte (KB)/secondo.
    last_redone_lsn Numero effettivo della sequenza di log dell'ultimo record di log che è stato rifatto nel database secondario. last_redone_lsn è sempre minore di last_hardened_lsn.
    last_received_lsn ID del blocco di log che identifica il punto fino al quale tutti i blocchi di log sono stati ricevuti dalla replica secondaria che ospita il database secondario. Riflette un ID blocco di log riempito con zeri. Non si tratta di un numero di sequenza di log effettivo.

    Ad esempio, eseguire la query seguente sulla replica primaria per segnalare la replica con la prima truncation_lsn ed è il limite superiore che il 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, a titolo esemplificativa, le seguenti:

    • Assicurarsi che non vi sia alcun collo di bottiglia di risorse o prestazioni nel database secondario.
    • Assicurarsi che il thread Redo non sia bloccato nel database secondario. Usare l'evento lock_redo_blocked esteso per identificare quando si verifica questo problema e su quali oggetti è bloccato il 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 il problema:

  • Eliminare il database dal gruppo di disponibilità per il database secondario danneggiato.

    Nota

    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.

    Nota

    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, provare a riabilitare la funzionalità.

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

  • Aumentare il valore di MaxSize per il file di log delle transazioni se è stato raggiunto e se è 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 R2
  • SQL Server 2016 Enterprise
  • SQL Server 2016 Standard
  • SQL Server 2017 Enterprise
  • SQL Server 2017 Standard Windows