question

flagamba avatar image
flagamba asked

how to determine the session associated with a transaction that is blocking the tempdb

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.
tempdbdmvsessions
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant, thanks for your answer. Generally speaking you're right, but in this particular case the view that you suggest does not contain anything useful, since there is' no reference to the spid reported by dbcc OPENTRAN. It seems that the transaction is an orphan and does not have a connected session. It 'important to replicate the example to fully understand the problem. Thanks anyway.
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
There is a dynamic management object specifically for translating between transactions and sessions, [sys.dm_tran_session_transactions][1]. That will allow you to make the link up that you're trying to make. [1]: http://msdn.microsoft.com/en-us/library/ms188739.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.