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

S’applique à :Azure SQL Database

Vous pouvez voir des erreurs 9002 ou 40552 lorsque le journal des transactions est plein et 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.

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 pour laquelle la troncation du journal des transactions de la base de données est empêchée :

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 le journal des transactions ne peut pas être tronqué dans les pools élastiques Azure SQL Database, libérer de l’espace pour le pool élastique peut être une solution. 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 peut fournir uniquement un soulagement temporaire jusqu’à ce que le journal des transactions augmente à nouveau.

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 :

1. 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.
2. 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.
3. 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.
4. 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).

Contenu connexe

• Comprendre et résoudre les problèmes de blocage d’Azure SQL Database
• Résolution des problèmes de connectivité et autres erreurs avec Azure SQL Database et Azure SQL Managed Instance
• Résoudre les erreurs de connexion temporaires dans Azure SQL Database et SQL Managed Instance
• Vidéo : Meilleures pratiques de chargement des données sur Azure SQL Database