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. With it you can track down active transactions in seconds.
Following SQL will return currently active transaction.
SELECT * FROM sys.dm_tran_session_transactions
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Subscribe to get the latest posts sent to your email.
SQL Server slow? Let's fix it together in about four hours.
In my Comprehensive Database Performance Health Check, you share your screen, I guide you, and we fix your biggest performance troublemakers. No passwords. No remote access.
You keep every script, and the know-how to fix it yourself next time.
Want your whole team to learn it?
My Practical Workshop has no PowerPoint, just live demos. My AI Enablement Workshop shows your team where AI saves hours, and where it quietly costs them.
Over 400 customers have worked with me this way, and not one shared a password. See all consulting options.
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
any ideas what’s going on with this issue?
Hi Pinal Dave,
Thanks a lot, it was very useful.
it’s really good DMV.Once get the Session_ID from the above DMV,use dbcc inputbuffer(sp_id) to get the exact transaction .
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.
Hi Pinal,
How to check the transations which were happened earlier…ie not active and executed some time ago.
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 :-)
If the database is suspect, it’s not opened for queries. but this query is not database level but shown sessions having transactions.