question

I noticed that the **DBCC OPENTRAN(tempdb)** returns the spid of the process that blocks the truncation of tempdb. The problem is that **DBCC INPUTBUFFER(spid)** gives me back the statement involved, but **NOT** the **TransactionId**. The **DMVS**, on the other hand, give me the **TransactionId** but **NOT** the spid involved: I've tried with ALL the DMVS that relate to the "offending" transactionId. Steps to reproduce the problem: - Create a very large table with an alphanumeric column - SELECT * FROM TABLE (nolock) ORDER BY alphaColumn DESC - While the query is running, check with perfmon that the tempdb grows from the point of view of utilization percentage. - After a time, the query returns the first few lines: stop the client after, say, 600 rows, but leave the cursor open. - Use the command dbcc OPENTRAN (tempdb): returns the spid of the transaction and with the following dbcc inputbuffer we can see the query that we are using.

   DBCC OPENTRAN(tempdb)

Transaction information for database 'tempdb'. Oldest active transaction: SPID (server process ID): 72 UID (user ID) : -1 Name : sort_init LSN : (15493:197650:455) Start time : May 5 2013 3:38:55:533PM SID : 0x01 DBCC INPUTBUFFER(72) EventType Parameters EventInfo -------------- ---------- ------------------------------------------------- Language Event 0 SELECT * FROM MyBigTable(nolock) ORDER BY alphaColumn DESC - Now we can use

SELECT        
   transaction_id,       
   database_id,        
   database_transaction_begin_time,        
   database_transaction_type,        
   database_transaction_log_record_count,        
   database_transaction_log_bytes_used,        
   database_transaction_log_bytes_reserved,        
   database_transaction_begin_lsn,        
   database_transaction_last_lsn  
FROM sys.dm_tran_database_transactions a  
WHERE database_transaction_log_bytes_used > 0

to obtain the transactionId. It happens that the trasaction id is found ONLY in the two DMV sys.dm_tran_database_transactions (already used) and sys.dm_tran_active_transactions which does not allow to trace the SESSION that launched the transaction. I hope I have explained the problem clearly. Thanks for help.