Partager via

Erreur 9002 : le journal des transactions de la base de données est plein en raison du message d’erreur AVAILABILITY_REPLICA 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 de produit d’origine : SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012
Numéro de base de connaissances d’origine : 2922898

Symptômes

Examinez le cas suivant :

  • Vous disposez de Microsoft SQL Server 2012 ou d’une version ultérieure installée sur un serveur.
  • L’instance de SQL Server est un réplica principal dans l’environnement groupes de disponibilité Always On.
  • 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 sur le réplica principal et vous recevez un message d’erreur semblable à ce qui suit :

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

Cause

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

Dépannage

Il existe deux scénarios qui peuvent entraîner une croissance de journalisation dans une base de données de disponibilité et les 'AVAILABILITY_REPLICA' log_reuse_wait_descéléments suivants :

  • Scénario 1 : latence fournissant des modifications journalisées à la version secondaire

    Lorsque les transactions changent de 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 au fichier journal de base de données sur le réplica secondaire. Le réplica principal ne peut pas remplacer les blocs journaux dans son propre fichier journal tant que ces blocs journaux 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 sur 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 niveau du réplica principal et provoque l’augmentation de l’utilisation de son fichier journal.

    Pour plus d’informations, consultez La latence réseau élevée ou le débit réseau faible entraîne l’accumulation du journal sur le réplica principal.

  • Scénario 2 : Latence de restauration automatique

    Une fois renforcé au fichier journal de base de données secondaire, un thread de restauration à nouveau dédié dans l’instance de réplica secondaire applique les enregistrements de journal contenus au ou les 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 automatique dans tous les réplicas secondaires n’ont pas appliqué les enregistrements de journal contenus.

    Si l’opération de rétablissement sur un réplica secondaire n’est pas en mesure de suivre la vitesse à laquelle les blocs de journaux sont renforcés à ce réplica secondaire, il entraîne la croissance du journal au niveau du réplica principal. Le réplica principal peut uniquement tronquer et réutiliser son propre journal des transactions jusqu’au point que tous les threads de rétablissement du réplica secondaire ont é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 secondaires pour identifier la base de données secondaire qui retarde la troncation du journal le plus.

    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 à nouveau. Certains champs clés sont les suivants :

    Champ Description
    log_send_queue_size Quantité d’enregistrements de journal qui n’ont pas atteint le 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 du réplica secondaire qui n’a pas encore été restauré, en kilo-octets (Ko).
    redo_rate Taux auquel les enregistrements de journal sont redoncés sur une base de données secondaire donnée, en kilo-octets (Ko)/seconde.
    last_redone_lsn Numéro séquentiel dans le journal réel du dernier enregistrement du journal qui a été restauré 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 vers lequel 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 rembourré avec des zéros. Ce n’est pas 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 tôt truncation_lsn et est la limite supérieure que le serveur 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 mais ne sont pas limitées aux éléments suivants :

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

Solution de contournement

Une fois que vous avez identifié la base de données secondaire qui effectue cette opération, 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 le secondaire incriminé.

    Note

    Cette méthode entraîne la perte du scénario de 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 modifiant le ALLOW_CONNECTIONS paramètre du SECONDARY_ROLE réplica sur NO.

    Note

    Cela empêche les utilisateurs de lire les données dans le réplica secondaire, qui est la cause racine du blocage. Une fois que la file d’attente de rétablissement a été supprimée à une taille acceptable, envisagez de réactiver la fonctionnalité.

  • Activez le paramètre de croissance automatique s’il est désactivé et qu’il existe un espace disque disponible.

  • Augmentez la valeur MaxSize du fichier journal des transactions si elle a été atteinte et qu’il existe un espace disque disponible.

  • Ajoutez un fichier journal des transactions supplémentaire si celui 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 Enterprise
  • SQL Server 2017 Standard Windows