Détecter une transaction active

Une transaction restée ouverte empêche SQL Server de réutiliser la partie du journal qu’elle occupe, et les verrous qu’elle a posés bloquent les autres sessions. Le cas typique est une application qui a ouvert une transaction et ne l’a jamais validée, ou un BEGIN TRAN lancé dans une fenêtre de SSMS puis oublié. Les commandes ci-dessous se lisent dans l’ordre : le journal se remplit, quelle base, quelle transaction, quelle session, et ce qu’elle a exécuté.

Le remplissage du journal

DBCC SQLPERF (LOGSPACE);

DBCC SQLPERF (LOGSPACE) renvoie une ligne par base : la taille du journal en Mo (Log Size (MB)) et le pourcentage utilisé (Log Space Used (%)). Une base dont le pourcentage monte sans redescendre après les sauvegardes du journal est la candidate. La commande demande la permission VIEW SERVER STATE.

Microsoft recommande désormais la vue sys.dm_db_log_space_usage, qui donne la même information, mais pour la base courante seulement :

USE [PachaDataFormation];

SELECT total_log_size_in_bytes / 1048576 AS taille_journal_mo,
       used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

Les causes d’attente de troncature du journal

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;

log_reuse_wait_desc indique ce qui empêche la troncature du journal. ACTIVE_TRANSACTION signifie qu’une transaction ouverte retient le journal. Les autres valeurs pointent ailleurs : LOG_BACKUP attend une sauvegarde du journal (bases en mode de récupération complet ou journalisé en bloc), REPLICATION ou AVAILABILITY_REPLICA une réplication ou un réplica en retard. La page de Microsoft sur le journal des transactions décrit toutes les valeurs.

La colonne ne porte qu’une valeur, que Microsoft décrit comme la raison constatée au dernier point de contrôle. Sur une base en mode complet, elle peut afficher LOG_BACKUP alors qu’une transaction est ouverte. Sur l’instance de test, avec une transaction ouverte, la colonne est restée à LOG_BACKUP après une première sauvegarde du journal, et n’est passée à ACTIVE_TRANSACTION qu’après la seconde. Ne concluez pas à l’absence de transaction ouverte sur la seule foi de LOG_BACKUP : passez à l’étape suivante.

La plus ancienne transaction d’une base

DBCC OPENTRAN (N'PachaDataFormation');

Sans argument, DBCC OPENTRAN examine la base courante. Il affiche la plus ancienne transaction active de la base, avec la session qui la porte (SPID), son nom (user_transaction pour un BEGIN TRAN sans nom), son LSN et son heure de début (Start time). Quand rien n’est ouvert, il répond No active open transactions.

DBCC OPENTRAN ne montre qu’une transaction, la plus ancienne : s’il y en a plusieurs, les suivantes n’apparaissent pas. Il demande d’être membre du rôle sysadmin ou du rôle db_owner de la base.

Les sessions et leurs transactions ouvertes

sp_who2 est souvent cité pour cette étape. Il liste toutes les sessions de l’instance :

EXEC sp_who2;

La procédure n’a pas de page de référence dans la documentation de Microsoft, qui la range parmi les commandes anciennes, et elle ne dit pas si une session a une transaction ouverte. Une session qui a lancé BEGIN TRAN, modifié des lignes, puis attend sans rien faire apparaît dans sp_who2 avec le statut sleeping et la commande AWAITING COMMAND, exactement comme une session inoffensive. Microsoft décrit ce cas dans le scénario 2 de son guide sur les blocages.

Les vues de gestion dynamique répondent directement. La requête suivante liste chaque transaction ouverte d’une autre session que la vôtre, la plus ancienne en premier, avec le volume de journal qu’elle a écrit et le dernier lot envoyé par la session :

SELECT st.session_id,
       s.login_name,
       s.host_name,
       s.program_name,
       s.status,
       s.open_transaction_count,
       at.transaction_begin_time,
       DB_NAME(dt.database_id) AS base,
       dt.database_transaction_log_bytes_used AS octets_journal,
       ib.event_info AS dernier_lot
FROM sys.dm_tran_session_transactions AS st
JOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_id
JOIN sys.dm_tran_active_transactions AS at ON at.transaction_id = st.transaction_id
LEFT JOIN sys.dm_tran_database_transactions AS dt ON dt.transaction_id = st.transaction_id
OUTER APPLY sys.dm_exec_input_buffer(st.session_id, NULL) AS ib
WHERE st.session_id <> @@SPID
ORDER BY at.transaction_begin_time;

Ce qu’il faut lire :

• status à sleeping avec open_transaction_count supérieur à 0 : la session ne fait rien et garde sa transaction ouverte, c’est la transaction oubliée ;
• transaction_begin_time : depuis quand la transaction retient le journal et ses verrous ;
• octets_journal : le volume de journal écrit dans cette base, qui donne une idée de ce qu’un rollback devra défaire ;
• host_name, program_name et login_name : de quoi retrouver l’application ou la personne responsable.

Une transaction qui touche plusieurs bases, tempdb comprise, produit une ligne par base. La requête demande la permission VIEW SERVER STATE, ou VIEW SERVER PERFORMANCE STATE à partir de SQL Server 2022.

Ce que la session a exécuté

Pour une session précise, remplacez 51 par le session_id relevé à l’étape précédente :

DECLARE @session_id smallint = 51; -- remplacez 51 par le session_id à examiner

SELECT event_type, event_info
FROM sys.dm_exec_input_buffer(@session_id, NULL);

DBCC INPUTBUFFER (@session_id);

Les deux renvoient le dernier lot envoyé par la session : son texte pour un lot SQL (Language Event), le nom de la procédure pour un appel RPC (RPC Event). sys.dm_exec_input_buffer existe depuis SQL Server 2014 SP2 et rend le texte complet, en nvarchar(max). DBCC INPUTBUFFER tronque le texte à 4 000 caractères ; il reste utile sur une instance plus ancienne.

Le dernier lot n’est pas forcément celui qui a ouvert la transaction. Si l’application a envoyé le BEGIN TRAN dans un lot, puis d’autres instructions, vous ne voyez que le dernier. Le texte aide surtout à reconnaître l’application et l’écran concernés.

Avant un KILL

KILL met fin à la session et annule sa transaction. Avant de l’exécuter, pesez la perte et le coût.

La perte : tout ce que la transaction a modifié depuis son BEGIN TRAN est annulé, et l’application perd sa connexion. Si elle était en train d’exécuter un lot, elle reçoit une erreur de gravité 21 (Cannot continue the execution because the session is in the kill state.) et les instructions suivantes du lot ne s’exécutent pas, COMMIT compris. Si la transaction était un traitement légitime en cours, il faudra le relancer depuis le début.

Le coût : la session ne disparaît pas tout de suite. SQL Server doit d’abord défaire, à partir du journal, tout ce que la transaction a écrit, et ses verrous sont gardés pendant ce temps. Sur l’instance de test, l’annulation d’un UPDATE d’un million de lignes (110 Mo de journal) a pris environ deux secondes, pour trois secondes d’exécution. Microsoft prévient qu’une modification qui a tourné une heure peut demander au moins une heure de rollback (Understand and resolve SQL Server blocking problems). Un second KILL ne l’interrompt pas, et redémarrer l’instance n’aide pas : la récupération au démarrage refait le même travail, base inaccessible pendant ce temps.

Avec la récupération accélérée de base de données (ADR, SQL Server 2019 et versions ultérieures), le rollback est instantané. Elle est désactivée par défaut dans SQL Server (Gérer la récupération accélérée de base de données) : vérifiez is_accelerated_database_recovery_on dans sys.databases.

KILL n’accepte pas de variable : écrivez le numéro de session en clair, en remplaçant 51 par le session_id relevé.

KILL 51; -- remplacez 51 par le session_id de la transaction à annuler

Pour suivre le rollback, utilisez WITH STATUSONLY sur le même numéro, qui affiche le pourcentage fait et le temps restant estimé :

KILL 51 WITH STATUSONLY; -- même session_id que le KILL

Ne relancez pas KILL sans WITH STATUSONLY pour suivre l’avancement : si le rollback se termine entre-temps et que le numéro est attribué à une nouvelle connexion, c’est elle que vous tuez. Quand le rollback est fini, WITH STATUSONLY répond par l’erreur 6120 (Rollback operation for Process ID ... is not in progress.). Pendant le rollback, sys.dm_exec_requests affiche la session avec le statut rollback et un percent_complete qui avance.

KILL demande la permission ALTER ANY CONNECTION, comprise dans les rôles sysadmin et processadmin. Vous ne pouvez pas tuer votre propre session.

Une fois la transaction terminée, le journal n’est pas libéré tout de suite en mode de récupération complet : la prochaine sauvegarde du journal le fait. Le how-to Identifier et régler les problèmes de journaux de transactions traite la suite.