« Erreur 9002. Le journal des transactions pour la base de données est saturé à cause de 'AVAILABILITY_REPLICA' » message d’erreur dans SQL Server

Symptômes

Prenons l’exemple du scénario suivant :

• Vous disposez de Microsoft SQL Server 2012 ou une version ultérieure est installé sur un serveur.

• L’instance de SQL Server est un réplica principal dans un environnement de groupes de disponibilité AlwaysOn.

• L’option d’étendue automatique pour les fichiers journaux des transactions est définie dans SQL Server.

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

Cause

Cela se produit lorsque les modifications enregistrées au réplica principal ne sont pas encore renforcées sur le réplica secondaire. Pour plus d’informations sur le processus de synchronisation de données dans l’environnement de AlwaysOn, vous pouvez consulter :

• Processus de synchronisation de données

Résolution des problèmes

Il y a deux scénarios qui peuvent entraîner l’augmentation du journal dans une base de données de disponibilité et de la log_reuse_wait_desc 'AVAILABILITY_REPLICA' :Scénario 1 : fourniture de latence ouvert une session modifications secondairelorsque les transactions de modifient des données dans le réplica principal, ces modifications sont encapsulées dans les blocs d’enregistrement de journal et ces blocs connectés sont livrés et renforcés la base de données fichier journal sur le réplica secondaire. Le réplica principal ne peuvent pas remplacer les blocs de journal dans son propre fichier journal jusqu'à ce que les blocs de journal ont été livrées et renforcées pour le fichier journal de base de données correspondant à tous les réplicas secondaires. Tout retard dans la livraison ou le renforcement de la sécurité de ces blocs à n’importe quel réplica dans le groupe de disponibilité entraînera empêchent la troncation de ces modifications enregistrées dans la base de données dans le réplica principal et son utilisation du fichier journal croître.

Pour plus d’informations, consultez la rubrique suivante dans la documentation de Microsoft :

Latence de réseau élevée ou débit réseau faible entraîne l’accumulation de journal sur le réplica principal

Le scénario 2 : répéter la latence Une fois renforcé dans le fichier journal de base de données secondaire, un thread dédié de rétablissement dans l’instance de réplication secondaire s’applique les enregistrements du journal contenues dans les fichiers de données correspondants. Le réplica principal ne peuvent pas remplacer les blocs de journal dans son propre fichier journal jusqu'à ce que tous les threads de rétablissement dans tous les réplicas secondaires ont appliqué les enregistrements de journal qu’il contient. Si l’opération de rétablissement sur n’importe quel réplica secondaire n’est pas en mesure de faire face à la vitesse à laquelle les blocs de journal sont renforcées au niveau de ce réplica secondaire, elle entraîne l’augmentation dans le réplica principal du journal. Le réplica principal peut uniquement tronquer et réutiliser son propre journal des transactions jusqu’au point de threads de rétablissement du secondaire de tous les réplicas ont appliquées. S’il y a plus d’une comparaison secondaire, la colonne truncation_lsn de la gestion dynamique de sys.dm_hadr_database_replica_states afficher plusieurs secondaires pour identifier la base de données secondaire retarde la troncature du journal le plus. Vous pouvez utiliser le tableau de bord AlwaysOn et vues de gestion dynamique sys.dm_hadr_database_replica_states pour aider à contrôler le journal Envoyer file d’attente et rétablissement la file d’attente. Certains champs de clé sont les suivants :

Par exemple, exécutez la requête suivante sur le réplica principal afin de signaler le réplica avec le truncation_lsn au plus tôt et est la limite supérieure que la principale puisse 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

Mesures correctives peuvent inclure, mais ne sont pas limitées à ce qui suit :

• Assurez-vous qu’il n’existe aucun goulet d’étranglement de performances ou de ressources sur le serveur secondaire.

• Assurez-vous que le thread de rétablissement n’est pas bloqué sur le serveur secondaire. Utilisez l’événement étendue lock_redo_blocked pour identifier lorsque cela se produit, et sur les objets que le thread de rétablissement est bloqué.

Solution de contournement

Après avoir identifié la base de données secondaire qui rend cela se produit, essayez une ou plusieurs des méthodes suivantes pour contourner ce problème temporairement :

• La base de données du groupe de disponibilité de prendre incriminé secondaire. Remarque : Cette méthode provoquera la perte du scénario de haute disponibilité/récupération d’urgence pour l’image secondaire. Vous devrez peut-être configurer le groupe de disponibilité à l’avenir.

• Si le thread de rétablissement est souvent bloqué, désactiver la fonctionnalité secondaire lisible en modifiant le paramètre ALLOW_CONNECTIONS de le SECONDARY_ROLE pour le réplica sur non. Remarque : Cela empêchera les utilisateurs de lire les données dans le réplica secondaire qui est la cause du blocage. Une fois que la file d’attente de rétablissement a supprimé une taille acceptable, envisagez d’activer la fonctionnalité à nouveau.

• Activer le paramètre d’étendue automatique si elle est désactivée et que l’espace disque disponible est.

• Augmentez la valeur MaxSize pour le fichier journal des transactions, si elle a été atteinte et que l’espace disque disponible est.

• Ajouter un fichier de journal de transaction supplémentaire si l’objet actuel a atteint le maximum de système de 2 To ou si l’espace supplémentaire est disponible sur un autre volume disponible.

Statut

Microsoft a confirmé l'existence de ce problème dans les produits Microsoft figurant dans la liste des produits concernés par cet article.

Informations supplémentaires

Pour plus d’informations sur la raison pour laquelle un journal des transactions augmente de manière inattendue ou est saturé dans SQL Server, cliquez sur le numéro ci-dessous pour afficher l’article correspondant dans la Base de connaissances Microsoft :

317375 le journal des transactions augmente de manière inattendue ou est saturé dans SQL ServerPour plus d’informations sur le problème de blocage d’opération de rétablissement, passez à la suivante :

AlwaysON - série de formation de HADRON : lock_redo_blocked/rétablir travailleur bloqué sur le réplica secondairePour plus d’informations sur les colonnes basées sur des AVAILABILITY_REPLICA log_reuse_wait , accédez au site Web MSDN suivant :

Les facteurs qui peuvent retarder la troncature du journalPour plus d’informations sur l’affichage de la sys.dm_hadr_database_replica_states , consultez le site Web Microsoft TechNet suivant :

Introduction à la fonction de sys.dm_hadr_database_replica_statesPour plus d’informations sur comment surveiller et résoudre les modifications enregistrées qui n’arrivent pas et ne sont pas appliquées dans un délai raisonnable, accédez au site Web TechNet suivant :

Analyseur de performances pour les groupes de disponibilité AlwaysOn