This article is half-done without your Comment! *** Please share your thoughts via Comment ***
The Microsoft Distributed Transaction Coordinator mostly use for distributed transactions.
A Distributed Transaction is a very simple transaction which executes between two or more servers.
For example, We started one transaction in Server one and same transaction is going to finish in Server two.
In this kind of situation, we require MSDTC enabled in SQL Server.
To enable distributed transactions across different servers, you need to configure MSDTC on both the servers.
The following is a list you need to check:
1. DTC service is running:
Please run “services.msc” from windows run dialog to check if it is running.
If it is stopped, please start it by right-clicking on it and choose “Start”.
2. Network DTC access is enabled:
Please open Distributed Transaction Coordinator using Control Panel -> Administrator Tools -> Component Services.
Right click on LocalDTC and make sure that Network DTC should be enabled.
3. DTC service is configured to be allowed to access network in firewall in the linking server:
Please make sure this service could access the network and could receive connection from other server.
4. XACT_ABORT is enabled:
We could enable XACT_ABORT with the following statement:
5. Please execute the status of “Ad Hoc Distributed Queries”:
“Ad Hoc Distributed Queries” should enabled
|
1 2 3 4 |
sp_configure 'show advanced options', 1 reconfigure GO sp_configure 'Ad Hoc Distributed Queries' |
If the value under run_value is 0, please execute the following script to enable it:
|
1 2 3 4 5 |
sp_configure 'show advanced options', 1 reconfigure GO sp_configure 'Ad Hoc Distributed Queries', 1 reconfigure |
