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 > 0to 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.