Résoudre les erreurs de journal des transactions avec Azure SQL Database

Article
10/29/2023

Les erreurs 9002 ou 40552 peuvent s’afficher quand le journal des transactions est plein et qu’il ne peut pas accepter de nouvelles transactions. Ces erreurs se produisent quand le journal des transactions de la base de données, managé par Azure SQL Database, dépasse les seuils d’espace et qu’il ne peut pas continuer à accepter des transactions. Ces erreurs sont similaires aux problèmes liés à un journal des transactions complet dans SQL Server, mais leur résolution est différente dans SQL Server, Azure SQL Database et Azure SQL Managed Instance.

Remarque

Cet article est axé sur Azure SQL Database. Azure SQL Database est basé sur la dernière version stable du moteur de base de données Microsoft SQL Server. Une grande partie du contenu est donc similaire, même si les options et les outils de résolution des problèmes peuvent différer de SQL Server.

Pour en savoir plus sur la résolution des problèmes d’un journal des transactions dans Azure SQL Managed Instance, consultez Résolution des erreurs du journal des transactions dans Azure SQL Managed Instance.

Pour plus d’informations sur la résolution des problèmes liés à un journal des transactions dans SQL Server, consultez Résoudre les problèmes liés à la saturation du journal des transactions (erreur 9002 SQL Server).

Sauvegardes automatisées et journal des transactions

Dans Azure SQL Database, les sauvegardes de journal des transactions sont effectuées automatiquement. Pour la fréquence, la rétention et plus d’informations, consultez Sauvegardes automatisées.

L’espace disque disponible, la croissance du fichier de base de données et l’emplacement des fichiers sont également managés, de sorte que les causes et les résolutions classiques des problèmes du journal des transactions diffèrent de SQL Server.

Comme pour SQL Server, le journal des transactions de chaque base de données est tronqué à chaque sauvegarde de journal réussie. La troncation laisse un espace vide dans le fichier journal, qui peut ensuite être utilisé pour les nouvelles transactions. Quand le fichier journal ne peut pas être tronqué par les sauvegardes du journal, le fichier journal augmente pour accueillir les nouvelles transactions. Si le fichier journal atteint la limite maximale dans Azure SQL Database, les nouvelles transactions d’écriture échouent.

Pour plus d’informations sur la taille des journaux de transactions, consultez :

Pour connaître les limites de ressources vCore d’une base de données unique, consultez l’article consacré aux limites de ressources pour les bases de données uniques suivant le modèle d’achat vCore.
Pour connaître les limites de ressources vCore des pools élastiques, consultez l’article consacré aux limites de ressources pour les pools élastiques suivant le modèle d’achat vCore.
Pour connaître les limites de ressources DTU d’une base de données unique, consultez l’article consacré aux limites de ressources pour les bases de données uniques suivant le modèle d’achat DTU.
Pour connaître les limites de ressources DTU des pools élastiques, consultez l’article consacré aux limites de ressources pour les pools élastiques suivant le modèle d’achat DTU.

Troncation du journal des transactions empêchée

Pour découvrir ce qui empêche la troncation du journal dans un cas donné, reportez-vous à log_reuse_wait_desc dans sys.databases. La description « log reuse wait » vous indique les conditions ou les causes qui empêchent la troncation du journal des transactions par une sauvegarde de fichier journal normale. Pour plus d’informations, consultez sys.databases (Transact-SQL).

SELECT [name], log_reuse_wait_desc FROM sys.databases;

Pour Azure SQL Database, il est recommandé de se connecter à une base de données utilisateur spécifique, plutôt qu’à la base de données master, pour exécuter cette requête.

Les valeurs suivantes de log_reuse_wait_desc dans sys.databases peuvent indiquer la raison du blocage de la troncation du journal des transactions de la base de données :

log_reuse_wait_desc	Diagnostic	Réponse requise
NOTHING	État classique. Rien de bloque la troncation du journal.	Non.
CHECKPOINT	Un point de contrôle est nécessaire pour la troncation du journal. Rare.	Aucune réponse n’est requise, sauf si le problème persiste. Si le problème persiste, soumettez une demande de support auprès du Support Azure.
LOG BACKUP	Une sauvegarde du journal est requise.	Aucune réponse n’est requise, sauf si le problème persiste. Si le problème persiste, soumettez une demande de support auprès du Support Azure.
ACTIVE BACKUP OR RESTORE	Une sauvegarde de base de données est en cours.	Aucune réponse n’est requise, sauf si le problème persiste. Si le problème persiste, soumettez une demande de support auprès du Support Azure.
ACTIVE TRANSACTION	Une transaction en cours empêche la troncation du journal.	Le fichier journal ne peut pas être tronqué en raison de transactions actives et/ou non validées. Consultez la section suivante.
REPLICATION	Dans Azure SQL Database, cela peut se produire si la capture des changements de données (CDC) est activée.	Interrogez sys.dm_cdc_errors et résolvez les erreurs. Si les problèmes ne peuvent pas être résolus, soumettez une demande de support auprès du Support Azure.
AVAILABILITY_REPLICA	La synchronisation avec le réplica secondaire est en cours.	Aucune réponse n’est requise, sauf si le problème persiste. Si le problème persiste, soumettez une demande de support auprès du Support Azure.

Troncation du journal empêchée par une transaction active

Le scénario le plus courant quand un journal des transactions ne peut pas accepter de nouvelles transactions est une transaction durable ou bloquée.

Exécutez cet exemple de requête pour rechercher les transactions non validées ou actives, et leurs propriétés.

Retourne des informations sur les propriétés de transaction à partir de sys.dm_tran_active_transactions.
Retourne les informations de connexion de session, à partir de sys.dm_exec_sessions.
Retourne les informations de requête (pour les requêtes actives) à partir de sys.dm_exec_requests. Vous pouvez également utiliser cette requête pour identifier les sessions bloquées, recherchez request_blocked_by. Pour plus d’informations, consultez Collecter les informations de blocage.
Retourne le texte de la requête actuelle ou le texte de la mémoire tampon d’entrée à l’aide des vues DMV sys.dm_exec_sql_text ou sys.dm_exec_input_buffer. Si les données retournées par le champ text de sys.dm_exec_sql_text ont la valeur NULL, cela signifie que la requête n’est pas en cours d’exécution mais qu’une transaction est en cours. Dans ce cas, le champ event_info de sys.dm_exec_input_buffer contient la dernière instruction passée au moteur de base de données.

SELECT [database_name] = db_name(s.database_id)
, tat.transaction_id, tat.transaction_begin_time, tst.session_id 
, session_open_transaction_count = tst.open_transaction_count
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime())
, input_buffer = ib.event_info
, request_text = CASE  WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN left(est.text, 4000)
                       ELSE    SUBSTRING ( est.[text],    r.statement_start_offset/2 + 1, 
                                           CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text])) 
                                                ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1
                                           END  )  END
, request_status = r.status
, request_blocked_by = r.blocking_session_id
, transaction_state = CASE tat.transaction_state    
                     WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                     WHEN 1 THEN 'The transaction has been initialized but has not started.'
                     WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                     WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                     WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'
                     WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                     WHEN 6 THEN 'The transaction has been committed.'
                     WHEN 7 THEN 'The transaction is being rolled back.'
                     WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name
, azure_dtc_state    --Applies to: Azure SQL Database only
             =    CASE tat.dtc_state 
                 WHEN 1 THEN 'ACTIVE'
                 WHEN 2 THEN 'PREPARED'
                 WHEN 3 THEN 'COMMITTED'
                 WHEN 4 THEN 'ABORTED'
                 WHEN 5 THEN 'RECOVERED' END
, transaction_type = CASE tat.transaction_type    WHEN 1 THEN 'Read/write transaction'
                                             WHEN 2 THEN 'Read-only transaction'
                                             WHEN 3 THEN 'System transaction'
                                             WHEN 4 THEN 'Distributed transaction' END
, tst.is_user_transaction
, local_or_distributed = CASE tst.is_local WHEN 1 THEN 'Local transaction, not distributed' WHEN 0 THEN 'Distributed transaction or an enlisted bound session transaction.' END
, transaction_uow    --for distributed transactions. 
, s.login_time, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
, session_cpu_time = s.cpu_time, session_logical_reads = s.logical_reads, session_reads = s.reads, session_writes = s.writes
, observed = sysdatetimeoffset()
FROM sys.dm_tran_active_transactions AS tat 
INNER JOIN sys.dm_tran_session_transactions AS tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions AS s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests AS r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib 
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) AS est;

Gestion des fichiers pour libérer de l’espace

Si une condition empêche la troncation du journal des transactions dans les pools élastiques Azure SQL Database, la solution peut en partie être de libérer de l’espace pour le pool élastique. Toutefois, il est indispensable de résoudre la racine de la condition empêchant la troncation du fichier journal de transactions. Dans certains cas, la création temporaire de plus d’espace disque permet l’exécution de transactions durables, ce qui supprime la condition empêchant la troncation du fichier journal des transactions lors d’une sauvegarde normale du journal des transactions. Toutefois, libérer de l’espace constitue seulement une solution temporaire jusqu’à ce que le journal des transactions augmente de nouveau en taille.

Pour plus d’informations sur la gestion de l’espace de fichier des bases de données et des pools élastiques, consultez Gérer l’espace de fichier des bases de données dans Azure SQL Database.

Erreur 40552 : La session a été arrêtée en raison de l’utilisation excessive de l’espace réservé au journal des transactions

40552: The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

Pour corriger ce problème, essayez les méthodes suivantes :

Le problème peut se produire dans n’importe quelle opération DML comme l’insertion, la mise à jour ou la suppression. Passez en revue la transaction pour éviter les écritures inutiles. Essayez de réduire le nombre de lignes qui sont sollicitées immédiatement en implémentant le traitement par lot ou en les fractionnant en plusieurs transactions plus petites. Pour en savoir plus, consultez l’article Comment utiliser le traitement par lots pour améliorer les performances des applications de base de données SQL.
Ce problème est dû à des opérations de reconstruction d’index. Pour éviter ce problème, assurez-vous que la formule suivante est vraie : (nombre de lignes concernées dans la table) multiplié par (taille moyenne du champ mis à jour en octets + 80) < 2 gigaoctets (Go). Pour les tables volumineuses, envisagez de créer des partitions et de procéder à la maintenance des index uniquement sur certaines partitions de la table. Pour plus d’informations, consultez l’article Créer des tables partitionnées et des index.
Si vous effectuez des insertions en bloc à l’aide de l’utilitaire bcp.exe ou de la classe System.Data.SqlClient.SqlBulkCopy, essayez d’utiliser les options -b batchsize ou BatchSize permettant de limiter le nombre de lignes copiées sur le serveur à chaque transaction. Pour plus d’informations, consultez bcp Utility.
Si vous regénérez un index avec l’instruction ALTER INDEX, utilisez les options SORT_IN_TEMPDB = ON, ONLINE = ON et RESUMABLE=ON. Avec les index pouvant être repris, la troncation du journal est plus fréquente. Pour plus d’informations, consultez ALTER INDEX (Transact-SQL).

Notes

Pour plus d’informations sur d’autres erreurs de gouvernance des ressources, consultez Erreurs de gouvernance des ressources.