SQL Server: How to see current transactions or requests

To check if there are any active transactions in SQL Server, you have several tools at your disposal depending on how deep you want to inspect.

---

✅ Option:1 See Transactions via sys.dm_tran_active_transactions

SELECT * FROM sys.dm_tran_active_transactions;


This gives you raw info about each active transaction, including internal ones. Focus on transaction_type:

Value	Type
1	Read/write transaction
2	Read-only transaction
3	System transaction
4	Distributed transaction

---

✅ Option:2 See Transactions by Session (sp_who2 or sys.dm_exec_requests)

EXEC sp_who2;


Look at sessions where BlkBy <> '' or Status = 'RUNNABLE' / 'SUSPENDED'.

OR

SELECT
    session_id,
    status,
    command,
    wait_type,
    blocking_session_id,
    transaction_id
FROM sys.dm_exec_requests
WHERE transaction_id IS NOT NULL;

---

✅ Option:3 Check Transaction Count in Current Session

If you want to check if your own session has open transactions:

SELECT @@TRANCOUNT AS OpenTransactions;

---

✅ Option 4: DBCC OPENTRAN (Old but Useful)

To see the oldest active transaction in a specific database:

USE YourDBName;
DBCC OPENTRAN;

This is great for identifying long-running or stuck transactions.

---

🚦 Common Scenarios to Investigate:

Symptom	Possible Cause
Blocking / Deadlocks	Uncommitted transactions
Long-running queries	Transactions not committed
Replication lag	Uncommitted open transactions
TempDB pressure	Large transaction rollbacks

---