How to enable MSDTC XA transactions with Powershell

Question: I've read your post on Enable XA transactions for SQL Server , which outlines how to enable XA transactions via the Computer Services module. I'd like to integrate the enabling of XA transactions as part of a scripted SQL Server installation, whoch includes enabling XA transactions for some applications. I'm using Powershell to develop the script . How can I see the current value and also change the value to enable XA ? Answer: It is possible to access and set the MSDTC XA Transactions values using Powershell . You need to make sure your current login has the... Read more →

Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction

How to recover a suspect database in SQL Server? I’ve restarted a SQL Server after an outage to discover a suspect database in SQL Server. A quick check on the SQL Error Logs reports Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (xxxxxxxxx). Fix MS DTC, and run recovery again. What should I do? Currently the database is in suspect mode and I have not done anything. Answer: Before you do anything check the last full backup you have in place. Although at this point you do not need to complete... Read more →

SQL Server – Deleting Distributed Orphaned Transactions

I outlined in MS DTC and sys.dm_tran_active_transactions and SQL Server detected a DTC/KTM in-doubt transaction with UOW , how orphaned distributed transactions are created. This query deals with alleviating the immediate problem . Root cause analysis should follow. Depending on the application , the orphaned transactions count can rise quickly. For example , a Document Management system integrating emails into an archive can create some high voltage sql server blocking. As the blocking increases other users can’t add records. If it’s a critical system , the helpdesk is suddenly busy with complaining users. Problems are exacerbated on the type of... Read more →