July 10, 2011 at 10:41 pm
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.
July 11, 2011 at 1:28 am
I think you're looking for Adam Machanic's sp_WhoIsActive.
It's a great script, you are going to like it.
-- Gianluca Sartori
July 14, 2011 at 11:35 pm
Awesome script.
Thank u so much.
July 15, 2011 at 12:58 am
You're welcome.
You should thank Adam instead of me! 🙂
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply