Erreur 9002 : Le journal des transactions de la base de données est plein en raison de AVAILABILITY_REPLICA message d’erreur dans SQL Server

  • Article

Cet article vous aide à résoudre l’erreur 9002 qui se produit lorsque le journal des transactions devient volumineux ou manque d’espace dans SQL Server.

Version du produit d’origine : SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012
Numéro de la base de connaissances d’origine : 2922898

Symptômes

Prenons l’exemple du scénario suivant :

  • Microsoft SQL Server 2012 ou une version ultérieure est installé sur un serveur.
  • Le instance de SQL Server est un réplica principal dans Always On environnement de groupes de disponibilité.
  • L’option de croissance automatique pour les fichiers journaux des transactions est définie dans SQL Server.

Dans ce scénario, le journal des transactions peut devenir volumineux et manquer d’espace disque ou dépasser l’option MaxSize définie pour le journal des transactions au réplica principal et vous recevez un message d’erreur semblable au suivant :

Erreur : 9002, Gravité : 17, État : 9. Le journal des transactions de la base de données '%.*ls' est plein en raison de 'AVAILABILITY_REPLICA'

Cause

Cela se produit lorsque les modifications enregistrées au réplica primaire ne sont pas encore renforcées sur le réplica secondaire. Pour plus d’informations sur le processus de synchronisation des données dans Always On environnement, consultez Processus de synchronisation des données.

Résolution des problèmes

Il existe deux scénarios qui peuvent entraîner la croissance des journaux dans une base de données de disponibilité et :'AVAILABILITY_REPLICA' log_reuse_wait_desc

  • Scénario 1 : Latence de remise des modifications journalisées au serveur secondaire

    Lorsque des transactions changent des données dans le réplica principal, ces modifications sont encapsulées dans des blocs d’enregistrement de journal et ces blocs journalisés sont remis et renforcés dans le fichier journal de base de données au réplica secondaire. Le réplica principal ne peut pas remplacer les blocs de journal dans son propre fichier journal tant que ces blocs n’ont pas été remis et renforcés au fichier journal de base de données correspondant dans tous les réplicas secondaires. Tout retard dans la remise ou le renforcement de ces blocs à n’importe quel réplica dans le groupe de disponibilité empêche la troncation de ces modifications journalisées dans la base de données au réplica principal et entraîne l’augmentation de l’utilisation du fichier journal.

    Pour plus d’informations, consultez La latence réseau élevée ou un débit réseau faible provoque l’accumulation de journaux sur le réplica principal.

  • Scénario 2 : Latence de restauration par progression

    Une fois renforcé au fichier journal de base de données secondaire, un thread de restauration par progression dédié dans le réplica instance secondaire applique les enregistrements de journal contenus aux fichiers de données correspondants. Le réplica principal ne peut pas remplacer les blocs de journal dans son propre fichier journal tant que tous les threads de restauration par progression dans tous les réplicas secondaires n’ont pas appliqué les enregistrements de journal contenus.

    Si l’opération de restauration sur un réplica secondaire n’est pas en mesure de suivre la vitesse à laquelle les blocs de journal sont renforcés à cette réplica secondaire, elle entraîne une croissance du journal au niveau du réplica primaire. Le réplica principal peut uniquement tronquer et réutiliser son propre journal des transactions jusqu’à ce que tous les threads de restauration par progression de l’réplica secondaire aient été appliqués. S’il existe plusieurs bases de données secondaires, comparez la truncation_lsn colonne de la sys.dm_hadr_database_replica_states vue de gestion dynamique sur les plusieurs bases de données secondaires pour identifier la base de données secondaire qui retarde le plus la troncation du journal.

    Vous pouvez utiliser le tableau de bord Always On et sys.dm_hadr_database_replica_states les vues de gestion dynamique pour surveiller la file d’attente d’envoi du journal et la file d’attente de restauration par progression. Voici quelques champs clés :

    Field Description
    log_send_queue_size Quantité d’enregistrements de journal qui ne sont pas arrivés au réplica secondaire
    log_send_rate Fréquence à laquelle les enregistrements de journal sont envoyés aux bases de données secondaires.
    redo_queue_size Quantité d’enregistrements de journal dans les fichiers journaux de la réplica secondaire qui n’a pas encore été refaite, en kilo-octets (Ko).
    redo_rate Vitesse à laquelle les enregistrements du journal sont refaits sur une base de données secondaire donnée, en kilo-octets (Ko)/seconde.
    last_redone_lsn Numéro de séquence de journal réel du dernier enregistrement de journal qui a été refait sur la base de données secondaire. last_redone_lsn est toujours inférieur à last_hardened_lsn.
    last_received_lsn ID de bloc de journal identifiant le point jusqu’auquel tous les blocs de journal ont été reçus par le réplica secondaire qui héberge cette base de données secondaire. Reflète un ID de bloc de journal rempli de zéros. Il ne s’agit pas d’un numéro de séquence de journal réel.

    Par exemple, exécutez la requête suivante sur le réplica principal pour signaler le réplica avec le plus ancien truncation_lsn et est la limite supérieure que le principal peut récupérer dans son propre journal des transactions :

    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

    Les mesures correctives peuvent inclure, sans s’y limiter, les éléments suivants :

    • Assurez-vous qu’il n’existe aucun goulot d’étranglement de ressources ou de performances au niveau de la base de données secondaire.
    • Assurez-vous que le thread Redo n’est pas bloqué au niveau de la base de données secondaire. Utilisez l’événement lock_redo_blocked étendu pour identifier quand cela se produit et sur quels objets le thread de restauration par progression est bloqué.

Solution de contournement

Après avoir identifié la base de données secondaire à l’origine de ce problème, essayez une ou plusieurs des méthodes suivantes pour contourner ce problème temporairement :

  • Retirez la base de données du groupe de disponibilité pour la base de données secondaire en cause.

    Remarque

    Cette méthode entraîne la perte du scénario haute disponibilité/récupération d’urgence pour le serveur secondaire. Vous devrez peut-être configurer à nouveau le groupe de disponibilité à l’avenir.

  • Si le thread de rétablissement est fréquemment bloqué, désactivez la Readable Secondary fonctionnalité en remplaçant le ALLOW_CONNECTIONS paramètre de pour SECONDARY_ROLE le réplica sur NON.

    Remarque

    Cela empêchera les utilisateurs de lire les données dans le réplica secondaire, ce qui est la cause racine du blocage. Une fois que la file d’attente de restauration par progression est tombée à une taille acceptable, envisagez d’activer à nouveau la fonctionnalité.

  • Activez le paramètre de croissance automatique s’il est désactivé et s’il y a de l’espace disque disponible.

  • Augmentez la valeur MaxSize pour le fichier journal des transactions si elle a été atteinte et qu’il y a de l’espace disque disponible.

  • Ajoutez un fichier journal des transactions supplémentaire si le fichier actuel a atteint le maximum système de 2 To ou si un espace supplémentaire est disponible sur un autre volume disponible.

Plus d’informations

S’applique à

  • 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 Entreprise
  • Windows standard SQL Server 2017