[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”
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.
Great script Paul. I added a column for database, since I would run this kind of thing on a system with 100s of databases.
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. :-)
Thanks Aaron – you’re right. I’ve updated the script.
It is very useful…Thanks PAUL…
Hi Paul,
Have you had a look at my Who is Active script? It does all of this and much, much more. To get the log record counts use the @get_transaction_info = 1 mode.
http://sqlblog.com/blogs/adam_machanic/archive/2009/12/03/who-is-active-v9-57-fast-comprehensive-dmv-collection.aspx
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.
Nice Script. I have been using a script very similar to this one. It works beautifully.
Nice script. You’re immortalized (by name) in my code library.
Very nice script Paul, it works fine!
@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.
Hi Paul,
Very useful script – a big thanks
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.
It does not open a transaction unless you touch a record. Even if you roun “select 1” it wont start a transaction.
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
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.
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],’?
Thanks – the WordPress code-formatting plugin interprets as a command and removes it. Fixed.
Thanks
Very Useful.
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.
Most likely because it’s using some temporary space in tempdb, which will have a very small log footprint.
The column ‘Log Records’ is displayed in the output, but missing in the query.
Ah – yes – fixed now. Thanks
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
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.
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
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.
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?
Hmm – not something I’m an expert on. You could try DBCC INPUTBUFFER or the newer sys.dm_exec_input_buffer.
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 ;(
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
Hi Umair, i’m faceing the same issue. Do you figure it out?
Hello Paul, do you any script to see all old blocking history.please.like atleast one week history. Please.
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.
Hello Paul, do you have any script to see all old blocking history.please.like atleast one week history. Please.
Hi Paul,
Thank you very much for a very useful scripts.
Ó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