Post reply

How to see the open transactions??

  • August 13, 2010 at 2:39 am

    #239020

    Hi

    If I run a statement like

    begin tran A

    update A set ID = 2 where ID = 1

    Now this is an open transaction as it is yet to be commited or rollback.

    I can use select * from sys.sysprocesses where open_tran=1 or dbcc opentran to see that there are open transactions. But I really don't get what is the text for open transaction.

    The problem with sysprocesses or sys.dm_exec_requests is that they store only current SQL_HANDLE for a given session. So if I actully run this statement:

    begin tran A

    update A set ID = 2 where ID = 1

    select 1

    the handle from sysrocesses can be used to get the text sth like :

    select * from sys.dm_exec_sql_text(0x060005005B7A3A21B8618413000000000000000000000000)

    but this handle will retuen 'Select 1' as text.

    So is there a way to see the actual statement which is open when there are other statements which have been run after the open transaction for given spid?

    thanks

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • August 13, 2010 at 4:36 am

    #1206493

    this can give you

    declare @spid int

    select @spid = spid from sys.sysprocesses where open_tran = 1

    dbcc inputbuffer(@spid)

    but it needs to be executed in another mgmt window

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • August 13, 2010 at 5:20 am

    #1206517

    Hi Bhuvenesh

    the inputBuffer gives just the last transaction for that spid. As i metioned, If I ran another statement in same sesin, where I opened transaction, then it will give me the last statement and not the open one....So it wont work in any ssms window

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • August 13, 2010 at 5:34 am

    #1206523

    SureshS_DBA (8/13/2010)

    So is there a way to see the actual statement which is open when there are other statements which have been run after the open transaction for given spid?

    No. Don't get confused between transactions and statements. There is an open transaction in the circumstance you describe, but no statements are 'open'. Transactions get committed or rolled back - not statements.

    You can use the DMVs to find all queries cached by the session in question (assuming they are still in cache) but there's no built-in way to scope those to an open transaction.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • August 13, 2010 at 5:41 am

    #1206527

    SureshS_DBA (8/13/2010)

    Hi Bhuvenesh

    the inputBuffer gives just the last transaction for that spid. As i metioned, If I ran another statement in same sesin, where I opened transaction, then it will give me the last statement and not the open one....So it wont work in any ssms window

    yes you are right , i just tested it

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • August 13, 2010 at 5:41 am

    #1206529

    Thanks for reply. I actually meant transaction only.I knew that only transaction can be open. I was just querious why didn't microsoft add a sql _handle in sys.dm_tran_active_transactions . this DMVdoesn't have much data, so no impact on performace and that could have been great 🙂

    But yes , you showeda way if it is in cache..thanks

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply