Error Log error log

Published on November 24th, 2014 | by Alejandro Cordero

0

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

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

 

0 will wait for infinite time

sp_configure 'remote query timeout', 0
go
reconfigure with override
go

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!

Tags:


About the Author

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



Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Back to Top ↑
  • Sign Up For Our Newsletter

    Stay up to date on the latest from SQL Server products and Features.

    Sign Up Now

    Sign up for mailing list and receive new posts directly to your inbox.

    We promise never to share or sell any of your personal information.

  • Recent Posts

  • Categories

  • Archives

  • Tags

  • Topics

  • Subscribe to our RSS Feed


    sqlturbo.com A SQL Server Web Blog
    Canada, Ontario, Ottawa.
    http://sqlturbo.com

    The Authors