Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a periodic event where lots of connections hang on TM REQUEST (from sys.dm_os_waiting_tasks) and make no progress. KILL on the affected session_id does nothing

The server runs database mirroring (2008R2). Pausing and resuming mirroring fixes the issue when it happens.

The session stuck in this state are unkill'able. The load on the mirror is the usual, not bursty. Once a high number of connections are stuck in this state, the system freezes up and the mirror stops replicating (all queries get blocked).

Anyone seen this before and know what is causing it? There is nothing in the error logs.

share|improve this question
    
What mode is mirroring configured as ? What are the wait types when this issue occurs ? Any chance that SQL Server is configured in fiber mode? –  Kin Jan 23 at 15:32
    
Mirroring is in Sync mode. No fibers. No other waits –  Thomas Kejser Jan 23 at 15:36
    
What was the resource_description set to? –  Thomas Stringer Jan 23 at 15:42
    
resource_description = NULL –  Thomas Kejser Jan 23 at 16:51
    
I noticed this too not too long ago(we run async, but we change it to sync when we do failover, or patching etc). Does the spid stay after the DBs are fully synchronized? –  Subhash Pant Jan 24 at 2:36
show 3 more comments

2 Answers 2

TM stands for Transaction Manager. The current command being set to TM Request means that the thread is currently servicing a DTC (Distributed Transaction Coordinator) request, either to enlist in a DTC transaction, or defect from it, or commit it, or so on. Distributed Transactions are not supported for Database Mirroring because it can't guarantee transaction integrity. Suppress your DTC transactions and this should go away.

share|improve this answer
    
Credit S. Ashwin, colleague, Microsoft SQL Support –  stacylaray Jan 25 at 5:49
    
How would I diagnose where this is coming from? The MSDTC logs on the server are empty –  Thomas Kejser Jan 25 at 9:37
    
Could the DTC get involved if I has a cross database transaction on the same instance? –  Thomas Kejser Jan 25 at 9:37
    
@ThomasKejser - is sp_configure 'Ad Hoc Distributed Queries' on? - Perhaps some system is issuing cross-database transactions using OPENQUERY ad-hoc statements or something? –  Max Vernon Jan 29 at 5:52
1  
Cross database transactions use the same fundamental code as DTC transactions use. The main difference is the lack of the DTC manager of coordination. Instead SQL Server elects the lowest DBID in the transaction to be the transaction coordinator. –  stacylaray Feb 9 at 6:07
show 1 more comment

Service Broker uses the SQL Servers transaction manager instead of Microsoft Distributed Transaction Coordinator (MS DTC). The transaction is not across instances but within an instance. Are you using Service Broker?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.