0

Long story short I've got my local development machine into a bit of a mess with MSDTC and I have the horrible red arrow in DCOM. I have an application that uses MSDTC and MSMQ, and it's got itself stuck. Just to be clear, the SQL Server instance is Express and it is running on my machine. Everything is local to my machine is this case for my development purposes.

I have uninstalled MSDTC, MSMQ and then reinstalled MSDTC and reset the log and then installed MSMQ again, however the issue remains. Clicking on COM+ gives the following error:

enter image description here

Delving into MSDTC I can see no active transactions, but curiously the summary totals show 5 transactions, even though none are in the list:

enter image description here

Moving over to SQL Server, the following commands all return no rows:

  • SELECT @@trancount
  • DBCC OPENTRAN
  • SELECT XACT_STATE()

However, the last query against sys.dm_tran_active_transactions returns 5 rows:

enter image description here

It looks like my machine was in the middle of a debugging session, I left it running and then early in the morning, my machine rebooted.

I would like to remove these orphaned transactions, but I'm not sure how. Can I just DELETE FROM sys.dm_tran_active_transactions without any negative impact so I can go back to using MSDTC without issues.

2
  • @DavidBrowne-Microsoft Reboot done several times as part of MSMQ / MSDTC reinstallation.
    – Rebecca
    Jun 1, 2021 at 16:18
  • Ok. I don't know what those transactions are, but they are read-only transactions in TempDb, and they show up in my SQL Server instances too, so they should be unrelated to your issue. Jun 1, 2021 at 16:43

1 Answer 1

-1

I don't know how to help you with the DCOM and MSDTC side, but I hope this helps on the SQL Server side.

I don't think that you can delete directly from sys.dm_tran_active_transactions; it's a system view and reflects what is in the system. Some of these system views reflect an underlying table, but I don't think this one does; SQL Server keeps it's transaction information in memory, and so I think you are reading information from memory (correct me if I'm wrong).

Your other queries that you have run have been session or database specific. sys.dm_tran_active_transactions is for the whole server. I would recommend working the problem by using the query found at https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-active-transactions-transact-sql?view=sql-server-ver15 to find what those transactions are

SELECT
  GETDATE() as now,
  DATEDIFF(SECOND, transaction_begin_time, GETDATE()) as tran_elapsed_time_seconds,
  st.session_id,
  txt.text, 
  *
FROM
  sys.dm_tran_active_transactions at
  INNER JOIN sys.dm_tran_session_transactions st ON st.transaction_id = at.transaction_id
  LEFT OUTER JOIN sys.dm_exec_sessions sess ON st.session_id = sess.session_id
  LEFT OUTER JOIN sys.dm_exec_connections conn ON conn.session_id = sess.session_id
    OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle)  AS txt
ORDER BY
  tran_elapsed_time_seconds DESC;

You should be able to find out what the logins are, what program they're running, and the text. With that you should be able to determine whether or not they truly are affecting your MSDTC or are just a red herring. Worse comes to worse you can try shutting down your SQL Server and that should get rid of any distributed transactions that the MSDTC would be having to handle.

If you are still having troubles I think you might have some luck asking about the DCOM and MSDTC over on https://serverfault.com/ where they might have more experience working with DCOM.

1
  • there are no rows returned by that query although if I outer join to sys.dm_tran_session_transactions I do get several rows. It's like they are just completely orphaned. I have obviously rebooted several times after reinstalling MSMQ and MSDTC. It hasn't helped. I'm guessing my easiest route is to uninstall SQL Express and then reinstall it
    – Rebecca
    Jun 1, 2021 at 16:17

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