Query Timeout Expired

If you ever use linked servers to run queries on a remote database server, you may run into this issue if your queries take longer than 10 minutes:  OLE DB provider “SQLNCLI11” for linked server xxxxx returned message “Query timeout expired” [SQLSTATE 01000] (Message 7412)

Below is a screenshot (sorry so small) of what I found in my job history while reviewing the logs.  It is somewhat deceiving because this timeout does not cause an error, but is considered a warning.  We found it because our overnight jobs were missing data, but no errors were found, actually looking through the job history showed what the issue was.  There are ways to program your T-Sql to actually throw an error if it times out, but we needed to have the job run no matter how long it took so that is why we increased the timeout value.

timeout

 

The default query timeout is 10 minutes (600 seconds).   To change this setting, all you need to do is right click on your instance in SSMS and click properties.

 

timout2

 

On the Connections page, there is a setting called “remote query timeout” which defaults to 600 seconds.  On my server, I increased the time to 1800 seconds which is 30 minutes.  For your environment, you need to calculate the longest running query that you think you may have and then add a little extra.  Here is a link from Books Online on more information about the setting:

https://technet.microsoft.com/en-us/library/ms177457(v=sql.105).aspx

 

timout3

 

If you ever get the remote query timeout alert, changing this setting will allow you to run remote queries longer than 10 minutes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s