Résoudre des erreurs du journal des transactions avec Azure SQL Managed Instance

  • Article

S’applique à :Azure SQL Managed Instance

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 Managed Instance, 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 se concentre sur Azure SQL Managed Instance. Azure SQL Managed Instance 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 Database, consultez Résolution des erreurs du journal des transactions dans Azure SQL Database.

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 Managed Instance, 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. Pour suivre les sauvegardes automatisées effectuées sur une instance managée SQL, consultez Surveiller l’activité de sauvegarde.

L’emplacement et le nom des fichiers de base de données ne peuvent pas être managés, mais les administrateurs peuvent gérer les fichiers de base de données et les paramètres de croissance automatique des fichiers. Les causes et résolutions classiques des problèmes de journal des transactions sont similaires à celles 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 du journal supprime les fichiers journaux virtuels (VLF) inactifs du journal des transactions, ce qui libère de l’espace à l’intérieur du fichier, mais ne modifie pas la taille du fichier sur le disque. L’espace vide dans le fichier journal 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 Managed Instance, les nouvelles transactions d’écriture échouent.

Dans Azure SQL Managed Instance, vous pouvez acheter un stockage complémentaire, indépendamment du calcul, jusqu’à une limite. Pour plus d’informations, consultez Gestion des fichiers pour libérer plus d’espace.

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;

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 Managed Instance, cela peut se produire si la réplication ou la capture des changements de données sont activées. Si le problème persiste, examinez les agents impliqués dans la fonctionnalité CDC ou la réplication. Pour la résolution des problèmes de capture des changements de données, interrogez les tâches dans msdb.dbo.cdc_jobs. Le cas échéant, ajoutez via sys.sp_cdc_add_job. Pour la réplication, reportez-vous à Résolution des problèmes de réplication transactionnelle. 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
, 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 Azure SQL Managed Instance, la solution peut en partie être de libérer de l’espace. 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.

Dans Azure SQL Managed Instance, vous pouvez acheter un stockage complémentaire, indépendamment du calcul, jusqu’à une limite. Par exemple, dans le Portail Azure, accédez à la page Calcul + Stockage pour augmenter le Stockage en Go. Pour plus d’informations sur les limites de taille du journal des transactions, consultez limites de ressources pour SQL Managed Instance. Pour plus d’informations, consultez Gérer l’espace des fichiers pour les bases de données dans Azure SQL Managed Instance.

Le stockage de sauvegarde n’est pas déduit de votre espace de stockage SQL Managed Instance. Le stockage de sauvegarde est indépendant de l’espace de stockage d’instance et n’est pas limité en taille.

Erreur 9002 : Le journal des transactions de la base de données est plein

9002: The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

L’erreur 9002 se produit dans SQL Server et dans Azure SQL Managed Instance pour les mêmes raisons.

La réponse adéquate à un journal des transactions saturé dépend en partie de la ou des conditions qui ont motivé le remplissage du journal.

Pour corriger l’erreur 9002, essayez les méthodes suivantes :

  • Le journal des transactions n’est pas tronqué et a augmenté pour remplir tout l’espace disponible.
    • Étant donné que les sauvegardes du journal des transactions dans Azure SQL Managed Instance sont automatiques, une autre opération doit empêcher la troncation de l’activité du journal des transactions. La réplication incomplète, le CDC ou la synchronisation du groupe de disponibilité peut empêcher la troncation ; voir Troncation du journal des transactions empêchée.
  • La taille de stockage réservée de la SQL Managed Instance est pleine et le journal des transactions ne peut pas croître.
  • La taille du journal des transactions est définie sur une valeur maximale fixe ou la croissance automatique est désactivée, et par conséquent vous ne pouvez pas croître.

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.

Bien que l’erreur 9002 soit plus courante que l’erreur 40552 dans Azure SQL Managed Instance, les deux peuvent se produire.

Pour corriger l’erreur 40552, 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 de l’application.
  • 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).

Étapes suivantes