Parcourir les aide-mémoire · SQL Server
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
- Problème
- Réponse rapide
- Code / commandes
- 1. Lister les transactions utilisateur ouvertes par ancienneté
- 2. Ajouter la requête en cours et le texte SQL
- 3. Trouver les sessions en veille avec des transactions ouvertes
- 4. Inspecter les détails de transaction d’une session
- 5. Trouver les sessions qui en bloquent d’autres et ont des transactions ouvertes
- 6. Afficher la transaction active la plus ancienne de la base de données courante
- 7. Vérifier pourquoi la réutilisation du journal des transactions est en attente
- 8. Inspecter le SQL récent d’un propriétaire de transaction en veille
- Interpréter le résultat
- Points à vérifier
- Transaction très ancienne
- Session en veille + transaction ouverte
- ACTIVE_TRANSACTION dans log_reuse_wait_desc
- La session bloquante détient une transaction ancienne
- Nombreuses sessions avec des transactions ouvertes
- Notes de production
- open_transaction_count est utile mais ne dit pas tout
- Une session en veille peut rester dangereuse
- Les transactions ouvertes peuvent affecter la réutilisation du journal
- Les transactions ouvertes peuvent augmenter le coût du rollback
- Les charges de travail avec versionnement peuvent aussi être affectées
- Les autorisations varient selon la version et la plateforme
- Erreurs fréquentes
- 1. Supposer que les sessions en veille sont inoffensives
- 2. Tuer le bloqueur avant d’évaluer le risque de rollback
- 3. Ne regarder que le texte SQL courant
- 4. Considérer DBCC OPENTRAN comme un inventaire complet
- 5. Ignorer les limites de transaction de l’application
- 6. Supposer que l’ancienneté d’une transaction suffit à prouver un problème
- Étape suivante
- 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.
Ce contenu vous a-t-il été utile ?
Que pourrions-nous améliorer ?
Merci — cela m’aide à améliorer Javi Builds.
Restez au courant
Recevez uniquement les nouvelles de Javi Builds qui vous intéressent.
Consultez votre boîte de réception
Je vous ai envoyé un lien de confirmation. Votre abonnement sera actif après confirmation.