Linked Server Time Out

Error Message:

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider ‘SQLOLEDB’ reported an error. [OLE/DB provider returned Timeout expired]

or

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider ‘SQLOLEDB’ reported an error. Execution terminated by the provider because a resource limit was reached. [OLE/DB provider returned message: Timeout expired]

 

Cause:

Error 7399 is generic, it is basically telling you something went wrong. You Can enable traceflag 7300 to get more details on the error message. This will give you more insight on what steps to follow. For instance you could now get an error message like below:

 

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider ‘SQLOLEDB’ reported an error. [OLE/DB provider returned message: Timeout expired] OLE DB error trace [OLE/DB Provider ‘SQLOLEDB’ IDBInitialize::Initialize returned 0x80004005: ].

Indicates that the server might not be reachable or your linked server configuration/ driver is not configured properly.

Or you could get

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider ‘SQLOLEDB’ reported an error. Execution terminated by the provider because a resource limit was reached. [OLE/DB provider returned message: Timeout expired] OLE DB error trace [OLE/DB Provider ‘SQLOLEDB’ ICommandText::Execute returned 0x80040e31: Execution terminated by the provider because a resource limit was reached.].

This means your query is taking more time than the threshold defined as default. Your query needs more time to complete before flagging it as time out.

Solution:

For scenario #1 check your linked server configuration, double check your server/IP information is correct, check your user and password and make sure you are able to run a simple query. Check pinging your remote server or telnet to the port SQL Server is using, typically 1433. Typically if telnet and ping ports are opened SQL Server should respond successfully. Please note if ports are are closed you won’t get response, but SQL Server might still be running. Ping/Telnet test is not 100% accurate because of this.

 

For Scenario #2, try increasing your remote query timeout threshold by running the following commands

set threshold to wait for 30 seconds

[code language=”sql”]
sp_configure ‘remote login timeout’, 30
go
reconfigure with override
go
[/code]

 

0 will wait for infinite time

[code language=”sql”]
sp_configure ‘remote query timeout’, 0
go
reconfigure with override
go
[/code]

Also consider doing a sql server tuning on the transaction, if it runs faster then you could avoid this error.

 

MSDN Reference: http://msdn.microsoft.com/en-us/library/ms188279.aspx

These are quick references by SQLTurbo, intended for quick review of commands
and quick solutions to common sql errors. This section does not elaborate
on topics deeply. However feel free to comment and we will try to make
suggestions as needed.

Also expect to read topics about  sql 2014 , sql tools , tsql tuning , sql performance tuning , sql monitor and more! Also coming soon, our SQL Server online training!



Alejandro Cordero
Author: Alejandro Cordero
SQL Server DBA with over 8 years of experience, developer for .Net , Certified Scrum Master and Entrepreneur.

Leave a Reply