none
INFORMATION FOR OPEN TRANSACTION

    Question

  • Hi, I have a situation here

    That is USING DBCC OPENTRAN console command we get a list of open transactions in the database if any.But Is there any possible to know what are the queries involved in transaction or what are the operations performing in the particular transaction.

    CAN anybody help??

    Wednesday, August 28, 2013 5:54 AM

Answers

All replies

  • Hello,

    See TechNet ScriptCenter:
    Active Transactions with Request Data
    Current processes and their SQL statements

    May be the scripts are helpful for you.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 28, 2013 6:01 AM
  • You can use @@TRANCOUNT  in your code. Please see this link:

    @@TRANCOUNT


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog

    Wednesday, August 28, 2013 6:03 AM
  • Hello,

    when you run DBCC OPENTRAN for particular DB if there is open transaction it will list out SPID.Then you can use below command to find out query behind it.

    DBCC INPUTBUFFER(SPID)


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, August 28, 2013 6:06 AM
  • Hi,

    As per Shanky's comment...  dbcc opentran will give you a spid which you can then use to find out more information about what's happening.

    For example, if your spid was 99 then you could run:

    sp_who2 99

    Which would let you know what application is running the spid, from which machine, and how hard it's hitting (cpu, reads, writes etc).

    Also you can use:

    dbcc inputbuffer(99)

    Which will tell you the command currently being run on that spid.

    For a fuller picture though, Google "Adam Machanic sp_whoIsActive" and download his script (here: http://sqlblog.com/files/folders/beta/entry42453.aspx).  It will tell you everything you could possibly want to know (including sql text, query plans etc) for anything currently running on your server.  This is one of the most commonly used scripts for DBAs.

    Thanks,

    Kevin

    Wednesday, August 28, 2013 6:44 AM