Script: open transactions with text and plans

[Edit 2017: this query is still entirely applicable on all current versions of SQL Server.]

Here’s a little script I knocked up this afternoon to tell me who has open transactions on the server – not just the single oldest active transaction that DBCC OPENTRAN returns.

It gives back:

  • session ID
  • login name
  • database context
  • transaction begin time
  • how many log records have been generated by the transaction
  • how much log space has been taken up by those log records
  • how much log space has been reserved in case the transaction rolls back
  • the last T-SQL that was executed in the context of the transaction
  • the last query plan that was executed (only for currently executing plans)

It’s ordered by the transaction begin time. I had some trouble using CROSS APPLY with the sys.dm_exec_query_plan DMV – if the plan isn’t available, it blows out the entire result-set for that transaction. After messing around for ten minutes I discovered the OUTER APPLY operator which works nicely with NULL values.

Here’s the script with some example output (note, there are no delimiters around ‘text’ in line 8 as that confuses the code-formatting plugin):

SELECT
    [s_tst].[session_id],
    [s_es].[login_name] AS [Login Name],
    DB_NAME (s_tdt.database_id) AS [Database],
    [s_tdt].[database_transaction_begin_time] AS [Begin Time],
    [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
    [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
    [s_est].text AS [Last T-SQL Text],
    [s_eqp].[query_plan] AS [Last Plan]
FROM
    sys.dm_tran_database_transactions [s_tdt]
JOIN
    sys.dm_tran_session_transactions [s_tst]
ON
    [s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
    sys.[dm_exec_sessions] [s_es]
ON
    [s_es].[session_id] = [s_tst].[session_id]
JOIN
    sys.dm_exec_connections [s_ec]
ON
    [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
    sys.dm_exec_requests [s_er]
ON
    [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
    sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY
    sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY
    [Begin Time] ASC;
GO
session_id Login Name        Database Begin Time              Log Bytes Log Rsvd Last T-SQL Text                      Last Plan
---------- ----------------- -------- ----------------------- --------- -------- ------------------------------------ ---------
54         ROADRUNNERPR\paul foo      2010-02-01 15:28:48.560 236       8550     begin tran insert into t1 values (1) NULL
55         ROADRUNNERPR\paul foo      2010-02-01 16:38:18.373 356       8852     insert into t1 values (3)            NULL

37 thoughts on “Script: open transactions with text and plans

  1. Very useful, thank you for sharing! Though I’m very surprised at the value for Log Bytes Reserved – it’s a lot higher than I thought it would be.

  2. Actually now that I ran it on several systems and did not observe a single query plan came back, I looked closer and suspect that you need to pass a plan_handle into sys.dm_exec_query_plan, not a sql_handle. So I changed the OUTER APPLY to this:

    LEFT OUTER JOIN sys.dm_exec_requests AS s_req
    ON s_ec.[session_id] = s_req.[session_id]
    OUTER APPLY sys.dm_exec_query_plan (s_req.plan_handle) AS s_eqp

    This means you’ll only get the plan for requests that are currently active, of course. And this makes the query take a little longer. :-)

  3. Great script, Paul, thanks. I’ve noticed on our system we often get a number of entries where the Begin Time is NULL, and all the log columns are zero. What does that mean?

    Thanks.

  4. @Adam Nope not yet – always been on my list to try out.
    @Jerry They’re read-only transactions that your code starts with an explicit BEGIN TRAN. SQL doesn’t know yet whether they’re read-only so has to count them in the active transaction list.

  5. Great script Paul!

    I ran a test and for me something unexpected happened. I had two windows one with begin tran and another with your script. However, begin tran did not register until some subsequent code was run in the window. I expected an open transaction after the begin tran but apparently not so. I would have thought the open tran would have written to the transaction log. As they say every days a school day.

  6. No – simply doing a BEGIN TRAN doesn’t do anything. The transction isn’t ‘active’ and hasn’t taken any locks. As soon as you do a SELECT inside the transaction, it will show up. Nothing gets written to the transaction log unless the transaction changes the database in some way. I’ll do a blog post about this.

    Cheers

  7. Thanks Paul!

    It makes sense. I am looking forward to your blog on this.

    For me this is possibly one of those areas Adam Machinac was referring to when he blogged about puzzling things that don’t behave the way you think.

  8. There may be an error in the script above. Look at this portion:

    ‘s_est. AS [Last T-SQL Text],’

    Shouldn’t this be ‘s_est.text AS [Last T-SQL Text],’?

  9. when I run this query, the logs* values show up as 0. and the same query is showing up against tempdb and another database on which it is actually being run. if the query is not using up any space, why is it showing up? also the query is not in a Transaction scope. It uses a CTE and works on a lot of data.

  10. Hi Paul,

    I am facing an open transaction issue. There is a complex code of a stored procedure (SP1) and it is calling other stored procedures (SP 2 Layers) and these other stored procedures (SP 2 Layers) are calling more child procedures (SP3 Layers) and might be these child procedures (SP3 Layers) are calling it’s sub child procedures (SP4 Layer).

    When we execute the SP1, a transaction gets open and it hold locks on database tables. I am not able to find the exact code/stored procedure name from where this open transaction was started.

    Could you please share any query which can exactly find the code/storedprocedure from where that open transaction was started.

    Thanks in advance.
    Shakti Singh

    1. You could use Extended Events to look for a lock being acquired on a specific table and then get the tsql callstack action to find out exactly where in the code it’s happening. I don’t have pre-written code I can post that does that.

  11. Paul, great script and I’m hoping to use this to help identify “potential blockers”.
    Am I right in thinking that these are all “open transactions” which could potentially be the head of a blocking chain?
    We have an issue with an application where if a user leaves their client open and then go for lunch, they can potentially block other users for the duration of their lunch break. We want to be able to identify who they might be ahead of time and advise them of the error of their ways.

    Out of interest, from the script you generated, would anything here represent “last batch time”? Maybe a max of last read and last write?
    Regards,

    Andy

    1. Yes – potentially, as they’ll all be holding locks of some sort. Nope – there’s nothing that I know of (IIRC) that will give you the last time something happened for the transaction from those DMVs. You’d need to have a trace or XEvents session to tell that.

  12. Hi Paul! Thanks for this big contribution to SQL community.

    Just one question: suppose that a session started a transaction and ran a statement (an INSERT for instance), and is currently running a heavy SELECT, leaving the INSERT uncommited.

    The SELECT will be depicted on sp_whoisactive with all details, but is it possible to catch the text of the first statement, without xEvents or traces?

  13. Hi Paul,

    Thanks for the script, it works perfectly.

    I have a problem of open transactions for one of the database, there are couple of sync queries which runs on the database and updates about 1200 records in 3 to 4 tables along with some selects (simple selects), sync queries keep on running and doesn’t end, when i execute your script or dbcc inputbuffer() i see sync related transactions (simple updates/inserts/selects), we dont use begin tran..commit, pages show timeout errors.

    I get same queries on tempdb and on actual DB as well

    I wonder how can i check where the problem is and which queries are keeping the open transactions, i am really stuck ;(

    1. If you’re not explicitly beginning/committing transactions, then whatever my script shows is the statement that’s the long-running transaction and you need to troubleshoot from there. More involved than just a blog comment – please post to a forum or let me know if you’d like some consulting help to figure it out. Thanks

  14. Hello Paul, do you any script to see all old blocking history.please.like atleast one week history. Please.

    1. There’s no way to do that unless you’re running something like my script every, say, 10-30 seconds and persisting the results. Another way would be to set up a blocked process report (Google it) and then you’ll have a record of blocking in your error log.

  15. Hello Paul, do you have any script to see all old blocking history.please.like atleast one week history. Please.

  16. Ótimo roteiro Paulo, fiz uma pequena mudança no código. Espero que seja útil

    Select
    sqlcmd.text AS [Sqlcmd T-SQL],
    s_bd.database_id As Database_ID,
    DB_NAME(s_bd.database_id) As Database_Name,
    s_trn.session_id As Session_ID,
    s_bd.database_transaction_begin_time As Begin_Time,
    cast((datediff (second,s_bd.database_transaction_begin_time, getdate()) / 3600 % 24) as varchar (10))+’:’+
    cast((datediff (second,s_bd.database_transaction_begin_time, getdate()) / 60 % 60) as varchar (10))+’:’+
    cast((datediff (second,s_bd.database_transaction_begin_time, getdate()) % 60) as varchar (10))
    As [Tempo (hh/mm/ss)],
    ss.login_name As Login_Name,
    s_trn.transaction_id As Transaction_ID,
    c.client_net_address As client_net_address,
    c.local_net_address As local_net_address,
    s_trn.open_transaction_count As Open_Transaction_Count
    FROM sys.dm_tran_database_transactions as s_bd

    Inner Join sys.dm_tran_session_transactions as s_trn on (s_trn.transaction_id = s_bd.transaction_id)

    Inner Join sys.dm_exec_Connections as c on (c.session_id = s_trn.session_id)

    Inner Join sys.dm_exec_sessions as ss on (ss.session_id = c.session_id)

    Cross Apply sys.dm_exec_sql_text (c.most_recent_sql_handle) AS [sqlcmd]
    order by s_bd.database_transaction_begin_time desc

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.