txn information - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

txn information

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-07-11 : 00:41:50

Hi All,

I have a question of transactions.

I have a 3 sessions opened in the management studio and executing 3 active txns which are uncommited.
something like below

session-1 SPID = 55
begin tran

declare @i numeric
set @i = 1
while @i <= 10000
begin
insert into t1(1,'abc')
set @i = @i + 1

end

session-2 SPID = 56
begin tran

declare @i numeric
set @i = 1
while @i <= 10000
begin
insert into t1(1,'abc')
set @i = @i + 1

end

session-3 SPID = 57

begin tran

declare @i numeric
set @i = 1
while @i <= 10000
begin
insert into t1(1,'abc')
set @i = @i + 1

end

now when i say dbcc opentran('dbname') i got 55 which is as expected i.e oldest active txns.

i looked into sp_who2 and sysprocesses table and saw these 3 spids running against the database
and status = "sleeping".

Am using sql 2005 and sql 2008.

Now my question is how to know or how to display the Active txns spids and what the command being run or still running?
Is there any dm views for this????

The reason i asked this question is looking dbcc opentran am getting oldest active tran but am not able to get all the active txns
and moreover in sp_who2 i see the spids status as "sleeping" but i really dont know whether they are done with commiting the txn or not.
So am looking for something from which i can able to get below information

- list of active txns
- what are the associated spid's or session ids
- what is the sql statement running behind
- from which app they are running the sql statement(S)
- from which host they are running

I can use profiler trace but am looking for something for really quick checks and something inline we can figure out what active txns and what they are doing.

Any help would be greatly appreciated.

Thank you.

Sachin.Nand

2937 Posts

Posted - 2011-07-11 : 03:06:37

Try this



select T2.session_id,T.text from sys.dm_tran_active_transactions T1
inner join sys.dm_tran_session_transactions T2
on T1.transaction_id=T2.transaction_id
inner join sys.sysprocesses s on s.spid=t2.session_id
cross apply(select * from sys.dm_exec_sql_text(sql_handle))T
where T2.enlist_count=1

The filter where T2.enlist_count=1 in the above query will give list of transaction which are currently executing.If you remove that filter it will give a list of transaction that has been executed in the all the sessions irreespective of whether they are completed or not.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources