SQL SERVER – 2005 -Track Down Active Transactions Using T-SQL

Just a day ago, I was wondering how many active transaction are currently in my database. I found following DMV very useful – very simple and to the point.

Following SQL will return currently active transaction.


Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL System Table, SQL Transactions
Previous Post
SQL SERVER – Introduction to Log Viewer
Next Post

Related Posts

30 Comments. Leave new

  • Dear Pinal,

    I am using SQL 2000. I tried executing the command.

    It gave following error :
    Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘sys.dm_tran_session_transactions’.

    I tried using Master and TempDB databases, but same error persisted.

    Kindly suggest.


  • can i attach a read only restored database. I restored a
    database Test in Stand by Mode. After that i detached that database and while restoring i m getting msg “Cannot attach a database that was
    being restored.”.
    Is it that i can not attach a readonly restored
    detached database ?

  • Kamran Shahid
    July 24, 2008 4:59 pm

    Nice Data Management View

  • Very useful. Thanks.

  • @Viraj

    That piece of sql doesn’t work in sql 2000. The Dynamic management view is one of the new features in SQL Server 2005 you can use to query the health of sql server 2005.

    For it to work, you have to run it in SQL server 2005 environment.

  • @Sandeep

    Because it is on standby mode, the restore is not finished yet. sql server is expecting you to
    apply more backups. when when you are about to apply the last backup,
    choose the RECOVERY option. This tells SQL server that the restore is finished.

    IN a situation where you have only one backup file to restore, you should always choose the RECOVERY option
    except when you are log shipping or mirroring database.

    To solve your problem, restore the DB with the RECOVERY option.
    You can then detach it and re-attach it.

  • Dear Emeka,

    Thanks 4 ur reply.
    I wonder, is there any similiar type of DMV or other similiar concept in SQL 2000 !!!


  • @Virag,

    With SQL Server 2000, Unfortunately no. However you can run a dbcc opentran(‘databasename’) on 2000/05.

    This will only give you the longest running transaction in that database.

  • hi pinal,
    i preparing for interview in sql server plz mail me on p.adhikari1981@gmail.com

    some query(technical) which is asked in interview

  • Hi pinal,

    I want to trace the users activity in database.I able to trace ddl transcation using trigger.But when i try to track the dml
    transaction using event_data() it will not work.
    so how to track the users dml activity or shall i run the sql profiler?.If yes pls give me the clear picture abt sql profiler

    pls mail me on gomsbox@yahoo.com


  • Hi All,
    I am writing stored procedure for transfering data from 5 staging tables to 5 master tables using sql server 2005 which are in same db and same server.
    I have following requirements
    –>I will first check the count in both staging and master tables
    –>if staging table count is greater than master table count then
    –>truncate master table records.
    –>then insert query executes which fetches records from staging to master table.
    Among 5 tables last two are dependent.
    For each staging to master table i am writnig individual

    But the problem is when i am executing this sql server getting very slow almost getting struck.

    Is this the correct way which i am following if not please suggest me the right way.
    its really urgent.
    Thanks in advance for any help

  • hi pinal sir,
    i am havng doubt regarding transactions……in oracle we need not give explicitly BEGIN TRAN………. In sqlserver 2000 also………we have to write BEGIN TRAN explicitly………
    i heard that in sqlserver 2000 also we need not write explicitly…..by setting some values………..how can i?………tell me the solution

  • i got the answer……….if anybody dont know then it will helpful

    for implicit-transactions

    set implicit_transactions on
    set implicit_transactions off

    example is(no need of begin tran)

    update authors
    set phone=’9866530517′
    where au_id=’756-30-7391′
    update publishers
    set city=’hyderabad’,country=’india’
    where pub_id=’9952′
    commit tran

  • hi dave

    i got an idea about active transaction’s count in a database

    with your query.but i have one query,how can i measure

    the “hit count” second for a paticular transaction in table

    like (it may be single updation,bulk copy of data’s insertion),

    so how can i measure the second’s count.

    Thanks in Advance


  • Dear Dave Pinal, I would like to know if there a instruccion or a way to list all triggers stored in database and see the status “enabled or disabled” of each one.

    thanks in advance,


  • Hi Julio,

    You can find all triggers status by this query in 2005:

    select name,
    CASE WHEN OBJECTPROPERTY (id, ‘ExecIsTriggerDisabled’) = 0
    THEN ‘Enabled’ ELSE ‘Disabled’ END
    from sysobjects
    where type = ‘tr’



  • Ty Tejas!!!

  • Hi Pinal sir i want to know the active count of the transaction in sql server 2000, moreover i want to know can it be possibel that transaction is taking 24hrs to get it commited

  • Hi,

    Can you give detail explaination on active transactions or can you reffer any doucment

  • Hi Pinal,
    only two questions please:

    1- how can i now the transactions which are causing locks in the sqlserver database,
    2- is it possible that an inner transaction and the main transaction generate a deadlock


Leave a Reply