September 24, 2010 at 7:49 am
This is kind of a rewording of a previous post of mine which was poorly worded and got no good response- please excuse me for posting such a similar question...
When I pull up Activity Monitor and see non zero values in the Open
Transactions column, and the status is sleeping, does this truly indicate
this SPID has an open, uncommitted transaction? In some cases I wait 10
minutes, 2 hours, or a week and the SPID still shows an open transaction..
DBCC Opentran does not show the open transaction, but I am seeing a large amount of used space in tempdb dedicated to version store.
September 24, 2010 at 7:53 am
In previous post- someone indicated that DBCC Opentran only includes logged transactions... so if this is a "select", there is nothing written to the transaction log and therefore doesnt appear in DBCC Opentran.
But even so, I expect these old open transactions are the likely cause for large growth of version store in tempdb...
this makes sense? this is the applications problem for not closing those transactions- not really a db problem- right?
September 24, 2010 at 9:15 am
NJ-DBA,
I think Pradeep was correct in the last thread that DBCC OPENTRAN reads the transaction log to see open transactions.
Perhaps one of Gail Shaw's queries here would help you?
http://www.sqlservercentral.com/Forums/Topic987056-146-1.aspx
Sincerely,
Dan B
September 24, 2010 at 9:16 am
When there is something you don't understand in SSMS, fire up a trace and spy on it 🙂
If i recall correctly the queries used by the activity monitor are not very hard and it's prety easy to find out from where the values in each columns comes from.
September 24, 2010 at 9:24 am
Instead of SSMS or Trace, try using the DMV's.
sys.dm_exec_requests will show open, active requests, not just open connections. If you have an uncommitted transaction, it'll be there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 24, 2010 at 9:25 am
Thanks for your help. Was about to start trace, but threw some of Gail's queries at it first... those are exactly what I was looking for.
Thanks again!
September 24, 2010 at 9:29 am
The following query, from Gail, showed all the connections with open transactions... this is the ammo I need to take back to the application vendor and say, "why are there connections which have done nothing in the past week, but still have open transactions:
SELECT *
FROM sys.dm_tran_session_transactions tst INNER JOIN sys.dm_exec_connections ec ON tst.session_id = ec.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply