Parcourir les aide-mémoire · SQL Server

Technologie: SQL ServerCatégorie: DépannageNiveau: IntermédiaireMis à jour

Trouver les transactions ouvertes

Identifiez les transactions ouvertes, trouvez les sessions qui les détiennent, mesurez leur ancienneté et reliez les transactions de longue durée aux blocages, à la croissance du journal et au comportement de l’application.

Sur cette page
  1. Problème
  2. Réponse rapide
  3. Code / commandes
  4. 1. Lister les transactions utilisateur ouvertes par ancienneté
  5. 2. Ajouter la requête en cours et le texte SQL
  6. 3. Trouver les sessions en veille avec des transactions ouvertes
  7. 4. Inspecter les détails de transaction d’une session
  8. 5. Trouver les sessions qui en bloquent d’autres et ont des transactions ouvertes
  9. 6. Afficher la transaction active la plus ancienne de la base de données courante
  10. 7. Vérifier pourquoi la réutilisation du journal des transactions est en attente
  11. 8. Inspecter le SQL récent d’un propriétaire de transaction en veille
  12. Interpréter le résultat
  13. Points à vérifier
  14. Transaction très ancienne
  15. Session en veille + transaction ouverte
  16. ACTIVE_TRANSACTION dans log_reuse_wait_desc
  17. La session bloquante détient une transaction ancienne
  18. Nombreuses sessions avec des transactions ouvertes
  19. Notes de production
  20. open_transaction_count est utile mais ne dit pas tout
  21. Une session en veille peut rester dangereuse
  22. Les transactions ouvertes peuvent affecter la réutilisation du journal
  23. Les transactions ouvertes peuvent augmenter le coût du rollback
  24. Les charges de travail avec versionnement peuvent aussi être affectées
  25. Les autorisations varient selon la version et la plateforme
  26. Erreurs fréquentes
  27. 1. Supposer que les sessions en veille sont inoffensives
  28. 2. Tuer le bloqueur avant d’évaluer le risque de rollback
  29. 3. Ne regarder que le texte SQL courant
  30. 4. Considérer DBCC OPENTRAN comme un inventaire complet
  31. 5. Ignorer les limites de transaction de l’application
  32. 6. Supposer que l’ancienneté d’une transaction suffit à prouver un problème
  33. Étape suivante
  34. Pour aller plus loin

Problème

SQL Server subit des blocages, le journal des transactions n’est pas tronqué comme prévu, une session semble inactive mais détient encore des verrous, ou vous soupçonnez qu’une application a laissé une transaction ouverte.

L’objectif est de répondre aux questions suivantes :

  • Quelles sessions ont actuellement des transactions ouvertes ?
  • Depuis combien de temps ces transactions sont-elles ouvertes ?
  • La session propriétaire est-elle active ou en veille (sleeping) ?
  • Que faisait la session ?
  • Une transaction ouverte bloque-t-elle d’autres sessions ?
  • Une transaction ancienne pourrait-elle contribuer à la croissance du journal des transactions ?

Réponse rapide

Commencez par joindre les DMV des sessions, des transactions de session et des transactions actives.

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,

    at.transaction_id,
    at.name AS transaction_name,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds,
    at.transaction_type,
    at.transaction_state
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
INNER JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
WHERE
    s.is_user_process = 1
ORDER BY
    at.transaction_begin_time;

Concentrez-vous d’abord sur les transactions les plus anciennes, en particulier lorsque la session propriétaire est sleeping ou bloque d’autres traitements.

Code / commandes

1. Lister les transactions utilisateur ouvertes par ancienneté

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,

    at.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds,
    at.transaction_type,
    at.transaction_state
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
INNER JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
WHERE
    s.is_user_process = 1
ORDER BY
    at.transaction_begin_time;

C’est le meilleur inventaire de premier niveau.

Les transactions les plus anciennes méritent votre attention, car elles peuvent :

  • conserver des verrous
  • retarder le nettoyage des versions
  • contribuer aux problèmes de réutilisation du journal
  • révéler une mauvaise gestion des transactions côté application

2. Ajouter la requête en cours et le texte SQL

Utilisez cette requête lorsque vous voulez savoir si le propriétaire de la transaction exécute activement un traitement.

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,

    at.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds,

    r.status AS request_status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time AS wait_ms,

    txt.text AS batch_text
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
INNER JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    s.is_user_process = 1
ORDER BY
    at.transaction_begin_time;

Important : une session peut détenir une transaction ouverte même lorsqu’elle n’a aucune requête active.

C’est pourquoi sys.dm_exec_requests est joint avec un LEFT JOIN.


3. Trouver les sessions en veille avec des transactions ouvertes

Elles sont particulièrement importantes lors des incidents de blocage.

SELECT
    s.session_id,
    s.status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,
    s.last_request_start_time,
    s.last_request_end_time,

    at.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
INNER JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
WHERE
    s.is_user_process = 1
    AND s.status = N'sleeping'
ORDER BY
    at.transaction_begin_time;

Une session en veille avec une transaction ancienne peut encore détenir des verrous.

Cela indique souvent :

  • un code applicatif qui n’a pas exécuté de commit ou de rollback
  • un pool de connexions avec un état de transaction inachevé
  • des transactions manuelles laissées ouvertes
  • une gestion des erreurs qui a ignoré le nettoyage

4. Inspecter les détails de transaction d’une session

DECLARE @session_id int = 57;

SELECT
    s.session_id,
    s.status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,

    st.transaction_id,
    st.is_user_transaction,
    st.is_local,

    at.name AS transaction_name,
    at.transaction_begin_time,
    at.transaction_type,
    at.transaction_state
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
LEFT JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
WHERE
    s.session_id = @session_id;

Utilisez cette requête après qu’un autre diagnostic a identifié un SPID suspect.


5. Trouver les sessions qui en bloquent d’autres et ont des transactions ouvertes

WITH blockers AS
(
    SELECT DISTINCT
        blocking_session_id AS session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0
)
SELECT
    b.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,

    at.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds
FROM blockers AS b
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = b.session_id
LEFT JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = b.session_id
LEFT JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
ORDER BY
    at.transaction_begin_time;

C’est un moyen rapide de relier les blocages à l’ancienneté des transactions.

Si le bloqueur principal est en veille et détient une transaction ancienne, la gestion des transactions par l’application devient un suspect de premier plan.


6. Afficher la transaction active la plus ancienne de la base de données courante

DBCC OPENTRAN est utile pour une vérification rapide au niveau de la base de données.

DBCC OPENTRAN;

Vous pouvez aussi cibler une base de données précise :

DBCC OPENTRAN (N'YourDatabase');

Utilisez cette commande lorsque la question principale est :

Quelle est la transaction active la plus ancienne dans cette base de données ?

Elle est particulièrement utile lors des investigations sur le journal des transactions.


7. Vérifier pourquoi la réutilisation du journal des transactions est en attente

Lorsque le journal grossit, examinez la raison de la non-réutilisation au niveau de la base de données.

SELECT
    name AS database_name,
    recovery_model_desc,
    log_reuse_wait_desc
FROM sys.databases
WHERE
    name = DB_NAME();

Si log_reuse_wait_desc = ACTIVE_TRANSACTION, une transaction ouverte empêche la réutilisation du journal.

Utilisez ensuite les requêtes de transaction ci-dessus pour identifier son propriétaire.


8. Inspecter le SQL récent d’un propriétaire de transaction en veille

Si la session n’a aucune requête en cours :

DBCC INPUTBUFFER (57);

Utilisez l’ID de session réel.

Cette commande peut fournir le contexte de l’instruction récente d’une session en veille.

Considérez-la comme un élément de contexte, pas comme un historique complet de la transaction.

Interpréter le résultat

Champ Pourquoi c’est important
session_id Session qui détient la transaction ou y participe.
session_status sleeping peut être particulièrement significatif lorsqu’une transaction reste ouverte.
open_transaction_count Nombre de transactions ouvertes au niveau de la session. Utile comme signal rapide.
transaction_id Identifiant interne de la transaction.
transaction_begin_time Moment où la transaction a démarré. Essentiel pour analyser l’ancienneté.
transaction_type Type de transaction enregistré par SQL Server.
transaction_state État interne actuel de la transaction.
last_request_end_time Aide à mesurer depuis combien de temps une session en veille est inactive depuis sa dernière requête.
blocking_session_id Indique si la requête active est elle-même bloquée.
log_reuse_wait_desc Raison, au niveau de la base de données, pour laquelle le journal des transactions ne peut pas actuellement réutiliser l’espace.

Points à vérifier

Transaction très ancienne

L’ancienneté compte.

Une transaction ancienne est plus suspecte lorsqu’elle s’accompagne de :

  • plaintes d’utilisateurs
  • blocages
  • croissance du journal
  • inactivité de la session
  • pression sur le magasin de versions (version store)

Ne définissez pas de seuil universel « trop ancien ». Une transaction de cinq minutes peut être normale dans un système et inacceptable dans un autre.

Session en veille + transaction ouverte

C’est l’un des constats les plus précieux.

Causes possibles :

  • commit manquant
  • rollback manquant
  • chemin d’exception
  • timeout côté client
  • transaction manuelle
  • problème de pool de connexions

ACTIVE_TRANSACTION dans log_reuse_wait_desc

Cela justifie fortement une investigation sur les transactions ouvertes.

Trouvez la transaction la plus ancienne et la session propriétaire avant d’agir.

La session bloquante détient une transaction ancienne

Cherchez en priorité à comprendre :

  • quelle application en est propriétaire
  • quelle instruction a démarré ou modifié la transaction
  • pourquoi elle n’a pas été validée
  • le coût du rollback en cas d’arrêt de la session

Nombreuses sessions avec des transactions ouvertes

Cela peut indiquer un modèle applicatif plutôt qu’un problème ponctuel.

Recherchez des éléments communs :

  • login
  • hôte
  • nom de programme
  • pool d’applications
  • chemin de code de gestion des transactions

Notes de production

open_transaction_count est utile mais ne dit pas tout

La sémantique des transactions peut être plus complexe qu’un simple entier.

Utilisez les DMV de transaction lorsque vous avez besoin de détails faisant autorité.

Une session en veille peut rester dangereuse

En veille signifie qu’aucune requête n’est en cours d’exécution.

Cela ne signifie pas :

  • aucun verrou
  • aucune transaction
  • aucun impact sur le journal

Inspectez toujours l’état des transactions.

Les transactions ouvertes peuvent affecter la réutilisation du journal

Les transactions actives de longue durée peuvent retarder la troncature et la réutilisation du journal des transactions.

Si le journal grossit, corrélez l’ancienneté des transactions avec :

sys.databases.log_reuse_wait_desc

Les transactions ouvertes peuvent augmenter le coût du rollback

Plus la transaction est longue et volumineuse, plus SQL Server peut avoir de travail à annuler si la session est arrêtée.

Avant d’utiliser KILL, comprenez :

  • l’ancienneté de la transaction
  • l’opération métier concernée
  • le volume de lignes modifiées
  • l’impact du rollback
  • le comportement des applications en aval

Les charges de travail avec versionnement peuvent aussi être affectées

Les transactions de longue durée peuvent interagir avec la rétention des versions de lignes et le nettoyage du magasin de versions (version store).

L’effet exact dépend des fonctionnalités d’isolation et de versionnement utilisées.

Les autorisations varient selon la version et la plateforme

Un accès étendu aux DMV de session et de transaction exige les autorisations appropriées sur l’état des performances ou du serveur.

Pour SQL Server 2022 et versions ultérieures, Microsoft a fait passer les exigences de nombreuses DMV à VIEW SERVER PERFORMANCE STATE.

Les versions plus anciennes utilisent généralement VIEW SERVER STATE.

Erreurs fréquentes

1. Supposer que les sessions en veille sont inoffensives

Une connexion en veille peut encore détenir une transaction ouverte et des verrous.

2. Tuer le bloqueur avant d’évaluer le risque de rollback

Un bloqueur porteur d’une transaction volumineuse peut entraîner un long rollback et un impact opérationnel plus important.

Comprenez d’abord.

3. Ne regarder que le texte SQL courant

La requête qui a initialement modifié les données est peut-être déjà terminée.

L’état de la transaction peut survivre à la requête active.

4. Considérer DBCC OPENTRAN comme un inventaire complet

Cette commande est utile pour identifier la transaction active la plus ancienne d’une base de données.

Pour l’ensemble des sessions et des transactions, utilisez les DMV.

5. Ignorer les limites de transaction de l’application

Les symptômes observés côté base de données proviennent souvent du code applicatif.

Vérifiez :

  • le début de la transaction
  • le commit ou le rollback
  • la gestion des exceptions
  • la gestion des timeouts
  • les connexions mises en pool

6. Supposer que l’ancienneté d’une transaction suffit à prouver un problème

Certaines charges de travail exécutent légitimement des transactions longues.

Combinez l’ancienneté avec les blocages, la réutilisation du journal, l’importance de la charge de travail et l’impact sur les ressources.

Étape suivante

Utilisez le profil de la transaction pour choisir la prochaine investigation.

  • Session en veille + transaction ancienne → examinez le comportement de l’application en matière de commit et de rollback.
  • Une transaction ouverte bloque d’autres sessions → analysez la chaîne de blocage et identifiez le bloqueur principal.
  • log_reuse_wait_desc = ACTIVE_TRANSACTION → identifiez la transaction active la plus ancienne et la session propriétaire.
  • La transaction active exécute encore un traitement → examinez le SQL courant, les attentes, le CPU, les lectures et le plan.
  • Une transaction volumineuse ou ancienne devra peut-être être arrêtée → estimez l’impact opérationnel et celui du rollback avant d’utiliser KILL.
  • Nombreuses transactions ouvertes similaires → examinez la gestion des transactions à l’échelle de l’application.
  • Aucun problème significatif de transaction ouverte → revenez à une analyse plus large des attentes, des blocages et de Query Store.

← Tous les aide-mémoire