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.
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.
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:
If you ever get the remote query timeout alert, changing this setting will allow you to run remote queries longer than 10 minutes.