SQL Server Performance

a list of all active transactions

Discussion in 'General Developer Questions' started by chopeen, Sep 22, 2004.

  1. chopeen Member

    I am sure I knew how to do it, but I can't remember this right now. And Google cannot help me, too. (And that, unlike problems with my memory, is really strange.)

    Is there a way to list all active transactions?

    I know the DBCC OPENTRAN command, but it can only show the oldest transaction.

    --

    Marek Grzenkowicz
  2. Luis Martin Moderator

    sp_who ACTIVE?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  3. derrickleggett New Member

    You can also use:

    SELECT * FROM sysprocesses WHERE status NOT IN ('background','sleeping')

    if you're feeling frisky.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  4. chopeen Member

    Thanks guys, but that's not what I meant.

    I will give you an example.

    Let's suppose something like this happens:
    connection 1 -- BEGIN TRAN UPDATE ...
    connection 2 -- BEGIN TRAN DELETE ...
    connection 3 -- a long-running INSERT (implicit transaction)
    What I need is a list similar this:
    CONNECTION SQL COMMAND STARTED
    1 UPDATE... ...
    2 DELETE... ...
    3 INSERT... ...
    --

    Marek Grzenkowicz
  5. satya Moderator

  6. chopeen Member

    quote:Originally posted by satya

    Why not try tweak SP_WHO2 to get the list.
    Do you mean that I should run sp_helptext 'sp_who2' and then tweak the SQL code I get this way?

    --

    Marek Grzenkowicz
  7. satya Moderator

  8. chopeen Member

    quote:Originally posted by satya

    Yes, for such tailor made requirement I would do.
    My previous post showed only an example of what I am looking for. I do not need those exact columns. I just need a list of active transactions. Something that will give me a list with N rows, when SELECT @@TRANCOUNT says that there are N transactions.

    I've just got sp_who2 code out of syscomments table. Less that 300 lines. It could be worse. I'll take a look at it.

    --

    Marek Grzenkowicz

Share This Page