get @@trancount for all users/ connections URGENT

Hi all,

Is there any way to get the @.@.trancount for a connection from outside the connection?

The reason i'm asking is that a customer just lost a days work, and there is nothing in any table from a certain time onward. One theoy is that a backup was restored, but we checked and that is not the case.

So another theory is that a certain sproc began a transaction, but never finished because of an error. (We had some strange timeouts as well, so this is quite plausible.)

So the question: Can i get a list of current connections with their trancount? I could just run this to see if a certain connection would never get back to zero to check the transaction theory.

Thanks in advance,

Gert-Jan

In 2005, you can use this query to see that information.

select der.session_id, der.wait_type, der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as varchar(30)) as databaseName,
des.program_name,

der.command as commandType,

execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand,

der.open_transaction_count
from sys.dm_exec_sessions des
join sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as execText
where des.session_id <> @.@.spid --eliminate the current connection

|||The DB is on 2K, do you have this script for 2K?|||

Yeah, it is in sysprocesses. The column is open_tran

select spid, open_tran

from master..sysprocesses

|||

Louis,

Thanks for the very quick response, i'll get back to you if you saved the day.

Regards Gert-Jan

|||

Hi Gert-Jan van der Kamp,

> So another theory is that a certain sproc began a transaction, but never finished because of an error.

> (We had some strange timeouts as well, so this is quite plausible.)

I think you are looking for open transactions. It that case, the "select" statement provided by Louis (hope we can have your new book about DMVs and DMFs soon), will not help you much because that session will not have a match in sys.dm_exec_requests. You can find an example in BOL, under the topic about "sys.dm_exec_sessions".

This is from BOL.

SELECT s.* FROM sys.dm_exec_sessions AS s WHERE EXISTS ( SELECT * FROM sys.dm_tran_session_transactions AS t WHERE t.session_id = s.session_id ) AND NOT EXISTS ( SELECT * FROM sys.dm_exec_requests AS r WHERE r.session_id = s.session_id );

Forgot to mention that in SS 2000 your choice is to use "dbcc opentran".AMB

sql