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.

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

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.

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

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, privacy policy and cookie policy

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