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
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 lasys.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 leALLOW_CONNECTIONS
paramètre de pourSECONDARY_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
Pour plus d’informations sur la raison pour laquelle un journal des transactions augmente de façon inattendue ou devient plein dans SQL Server, consultez Résoudre les problèmes d’un journal des transactions complet (SQL Server l’erreur 9002).
Pour plus d’informations sur le problème de blocage des opérations de restauration par progression, consultez AlwaysON - Série d’apprentissage HADRON : lock_redo_blocked/redo worker bloqué sur le réplica secondaire.
Pour plus d’informations sur les colonnes log_reuse_wait basées sur AVAILABILITY_REPLICA, consultez Facteurs qui peuvent retarder la troncation du journal.
Pour plus d’informations sur la
sys.dm_hadr_database_replica_states
vue, consultez sys.dm_hadr_database_replica_states (Transact-SQL).Pour plus d’informations sur la façon de surveiller et de résoudre les problèmes liés aux modifications journalisées qui n’arrivent pas et ne sont pas appliquées en temps voulu, consultez Surveiller les performances des groupes de disponibilité Always On.
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