Resource limit on SQL Server Linked Server

Question

I'm frequently receiving the error on a stored procedure that uses openquery to read via a linked server.

The OLE DB provider "SQLNCLI11" for linked server "BrackleyICS" reported an error. Execution terminated by the provider because a resource limit was reached.

This will usually happen at 10.01 minutes. This would imply a timeout setting, however on other occasions it will run fine taking 35 minutes to complete.

Has anyone encountered this?

Answer 1

You can check you current timeout settings by:

query timeout

right click server > Properties > Connections > Remote Query Timeout

login timeout

right click server > Properties > Advanced > Remote Login Timeout

I think your login timeout is set to 10 mins, you need to increase this by running below script, change value from 30 seconds to required one

sp_configure 'remote login timeout', 30
go 
reconfigure with override 
go 

Reason why it is not timeouts every time: Not sure but if user ids logged on to server then timeout doesn't happen.