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 (http://blog.SQLAuthority.com)

30 thoughts on “SQL SERVER – 2005 -Track Down Active Transactions Using T-SQL

  1. 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.



  2. 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 ?


  3. @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.


  4. @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.


  5. Dear Emeka,

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



  6. @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.


  7. 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



  8. 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


  9. 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


  10. 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


  11. 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



  12. 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,



  13. 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’




  14. 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


  15. 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


  16. Hello there,
    I’m needing u’re help, I been doing a research for about 2 days now, but I can find an accurate answer here’s my issue:

    I have created a new SQL Server package then a new job and new step were I use the package created, i’m getting the following error message : Login failed for user ‘SYSADM’. [CLIENT: ], user SYSADM have cheked all server roles, also have checked all database role membership, dont know what to do in order to run a JOB, iI have realized that authentication mode, when I add a step I select use SQL Server Authentication on GENERAL TAB, but always change to use windows authentication.

    I hope u’ll understand my poor english.

    Best Regards Julio


  17. Hi, can anyone help me with a script showing,
    [1] Last accessed/Modified/Updated/read/write on a SQL Database
    [2] Last executed transactions on databases
    [3] User information who executed it.
    [4] All the databases information for that particular server.

    Thanks a ton.


  18. Hi All ,

    can i fire this command, where database is in suspect Mode ?

    During that time , I would like to find what and all transactions are not committed..so that we can kill those corrupted transactions ..

    Suggestion are welcome :-)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s