When executing a stored procedure across a linked server, the procedure appears to time-out but no error message returned.

I have two SQL 2012 Standard servers with linked servers defined between the two. I have enabled RPC and RPC Out on both linked servers. When calling a procedure on Server 2 from Server 1 (i.e. "execute [Server-2].[DB].[Schema].[procedure] @sError = @sReturn out;") , we sometimes experience what appears to be a time-out in that the execution of the procedure on Server 2 does not complete but the calling procedure on Server 1 appears to think it finished successfully. The procedure on Server 2 seems to just stop running in the middle of execution without any errors reported via various try-catch blocks. This seems to happen when large amounts of data are being processed via the procedure on Server 2, but finishes successfully when smaller data sets are being processed. Anyone have any advice? Thanks.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey ♦♦ commented · Aug 29, 2013 at 05:39 PM

Hard to supply a definitive answer. Instead I can recommend a few troubleshooting suggestions. When the process is apparently stopped, but still connected, validate that you have connections and what those connections are waiting on by querying sys.dm_exec_query_stats. Alternatively, capture the rpc start and stop events using extended events on both servers in order to see when you have a mismatched pair.

1 ·