Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

HOW TO FIND the OFFENDING SESSION FROM sys.dm_tran_database transaction Expand / Collapse
Posted Saturday, May 4, 2013 2:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 1, 2015 4:37 AM
Points: 2, Visits: 71
Investigating on the use of tempdb with the following very simple query:

select convert(char(15), db_name(database_id)) as dbname
from sys.dm_tran_database_transactions
where database_transaction_log_bytes_used > 0

dbname transaction_id database_transaction_begin_time database_transaction_log_bytes_used
--------------- -------------------- ------------------------------- -----------------------------------
tempdb 4078898703 2013-05-04 17:41:57.570 149539028

I can't find the owner session of transaction #4078898703
But with the following query:

,substring(text,1, 18)
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text (most_recent_sql_handle)
WHERE last_read between '20130504 17:40:00' and '20130504 17:42:00'

87 2013-05-04 17:41:56.170 2013-05-04 17:41:56.207 CREATE Procedure ...

The offending session is #87 (probably)

Why I can't find a reference to transaction #4078898703 in ALL the DMV?

Post #1449467
Posted Monday, May 6, 2013 5:44 AM



Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 17,173, Visits: 32,141
I think this might be posted on Ask.SQLServerCentral too. But you want to use sys.dm_tran_session_transactions. That will let you get transactions for a session or a session for a transaction.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1449674
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse