Stored Procedure performs a Linked Server update inside a Transaction. It works well for multiple client environments except for one particular client environment.
The behaviour that occurs is that the SP is called via ASP.net UI and it hangs until it reaches the command time-out of 30 seconds. Running the sp_WhoIsActive script reveals that the SP is stuck in the UPDATE LS statement with wait_info of PREEMPTIVE_COM_QUERYINTERFACE.
The strange thing is that after the initial time-out upon trying the operation again in the UI, it is then successful. But the time-out issue pops-up again sporadically without a clear pattern.
It seems to be related to MSDTC because of the statement in which the wait event occurs. It could be a permission or configuration setting since in other environments, this issue is not encountered. But MSDTC seems to be properly set-up since sometimes the execution is successful. Does anyone have experience with this PREEMPTIVE_COM_QUERYINTERFACE wait info?
The databases involved for local and remote are both SQL Server 2008 R2 databases using SQL Native Client 10.0 for the LS object.
Update: We checked the Windows Application Logs and during the time that the SP hanged; there is this error message.
EVENT ID 4359 MS DTC is unable to communicate with MS DTC on a remote system. MS DTC on the primary system established an RPC binding with MS DTC on the secondary system. However, the secondary system did not create the reverse RPC binding to the primary MS DTC system before timeout period expired. Please ensure that there is network connectivity between the two systems. Error Specifics:
Checking already done:
There is Symantec Endpoint Protection installed in the machines so we are investigating if that could be a factor.
Update for this:
We checked the Windows Application logs in both machines and it turns out that there is MSDTC Error Event ID 4359.
“MS DTC is unable to communicate with MS DTC on a remote system. MS DTC on the primary system established an RPC binding with MS DTC on the secondary system. However, the secondary system did not create the reverse RPC binding to the primary MS DTC system before the timeout period expired. Please ensure that there is network connectivity between the two systems. Error Specifics:”
There appears to be port blocking between the Primary MSDTC and Secondary MSDTC and we suspect that it may be due to the Symantec Endpoint Protection installed. So it just a client environmental issue that will be addressed.