Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
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: Wednesday, April 16, 2014 7:47 AM
Points: 2, Visits: 61
Investigating on the use of tempdb with the following very simple query:

select convert(char(15), db_name(database_id)) as dbname
,transaction_id
,database_transaction_begin_time
,database_transaction_log_bytes_used
from sys.dm_tran_database_transactions
where database_transaction_log_bytes_used > 0
[/font]

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:

SELECT
session_id
,connect_time
,last_read
,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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:03 PM
Points: 15,729, Visits: 28,132
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

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

Add to briefcase

Permissions Expand / Collapse