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.

SELECT *
FROM
sys.dm_tran_session_transactions

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

, ,
Previous Post
SQL SERVER – Introduction to Log Viewer
Next Post
SQL SERVER – DBCC SHRINKFILE Takes Long Time to Run

Related Posts

30 Comments. Leave new

  • 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

    Reply
  • Hi Pinal Dave,

    Thanks a lot, it was very useful.

    Reply
  • it’s really good DMV.Once get the Session_ID from the above DMV,use dbcc inputbuffer(sp_id) to get the exact transaction .

    Reply
  • Nagavara Arekatla
    January 11, 2013 4:44 pm

    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.

    Reply
  • Hi Pinal,

    How to check the transations which were happened earlier…ie not active and executed some time ago.

    Reply
  • 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 :-)

    Reply
    • If the database is suspect, it’s not opened for queries. but this query is not database level but shown sessions having transactions.

      Reply

Leave a Reply

Menu