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)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
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.