12

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.

7
  • 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 Shah
    Jan 23, 2014 at 15:32
  • Mirroring is in Sync mode. No fibers. No other waits Jan 23, 2014 at 15:36
  • What was the resource_description set to? Jan 23, 2014 at 15:42
  • 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? Jan 24, 2014 at 2:36
  • The SPID disappear after the pause. But they are unkillable while the problem is going on Jan 24, 2014 at 7:12

2 Answers 2

10
+50

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.

7
  • Credit S. Ashwin, colleague, Microsoft SQL Support
    – stacylaray
    Jan 25, 2014 at 5:49
  • How would I diagnose where this is coming from? The MSDTC logs on the server are empty Jan 25, 2014 at 9:37
  • Could the DTC get involved if I has a cross database transaction on the same instance? Jan 25, 2014 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?
    – Hannah Vernon
    Jan 29, 2014 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, 2014 at 6:07
0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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