DELETE FROM sys.dm_tran_active_transactions safe? [duplicate]

Question

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:

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

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:

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.

Answer 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.