December 19, 2012 at 9:08 am
A procedure that has worked for year had just stopped working the other day and I have narrowed down the issue to an update over linked server although I have not yet found why it has stopped working.
This is an example of the failing statement (real statement has many more joins on both remote and local dbs). Procedure works fine in development so I am not too concerned about the logic.
UPDATE a
SET a.enddate = GETDATE()
FROM [SERVER].[database].[dbo].
AS a
WHERE NOT EXISTS(SELECT * FROM [localdb].[dbo].
AS b
INNER JOIN [localdb].[dbo].[table2] AS c
ON b.id = c.id AND a.key = c.id)
The error returned to SSMS is:
OLE DB provider "SQLNCLI10" for linked server "SERVER" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
and the error in the log is:
Error: 7393, Severity: 16, State: 1.
The OLE DB provider "SQLNCLI10" for linked server "SERVER" reported an error 0x8000FFFF aborting the current transaction.
I ran a profiler trace on the remote server while executing the statement and it seem to evaluate this part of the join a.key = c.id then stop without any wait locks or blocks. On the local server the statement is waiting on OLEDB. I have even ran a packet capture on the source without any indication of why it is failing.
This is not executing as a distributed transaction. I thought it could be a DTC issue but DTC is not configured on the remote server.
Is there anything else I could check or monitor to identify why it is failing?
December 19, 2012 at 11:29 am
I have found the problem. The remote server is a VM and the VM was recently move to a different host and it's virtual switch was changed from being a standard switch to a distributed switch. The distributed switch is what caused the issue. Similar to another problem i had posted about (http://www.sqlservercentral.com/Forums/Topic1394090-391-1.aspx)
So it appears that Distributed switching in VMWare does not play nice with certain sql server processes.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy