2

We have two separate SQL Servers. On one server we have a data warehouse (DWH), on the other we have sales information database.

Now on the DWH server there is an ETL job that collects the information from the sales server. The job runs daily after midnight. The DWH collects the information via linked server from the sales database.

Now, the most time the ETL job runs without any problems. But sometimes it fails because of query timeout. We have found out, that there is a specific pattern: The failure happens every 11th day. So on the 11th day the ETL job fails to collect information.

The following error occurs:

SQLNCLI11 for linked server “my linked server” returned message “Query timeout expired.”

Note: The job fails usually 10 minutes after start.

We have searched everything and could not found out what this issue causes. Also we know that the amount of data is every time, almost the same. Also there is no any scheduled job that runs all 11 days or something.

The remote query timeout on the linked server is set to 0.

Our next step will be, to turn off the antivirus programm on the sales server, to check if this causes the problem.

Does anyone have any clue or idea, where i can search further to find the problem?

  • A microsoft supporter has already checked the server logs and he didnt find anything special. We will check the logs on the next 11th day again. – Leon Apr 24 at 14:07
3

This doesn't answer your question with regards to why the query is slow on the 11th day, but hopefully it helps clarify why it fails after 10 minutes.

You mention:

The remote timeout on the linked server is set to 0.

Intuitively, this might seem like there is no limit.

What it actually does is use the sp_configure default for remote query timeout, which is 600 seconds (10 minutes).

Setting the query timeout on the linked server to a higher value, perhaps 1200 seconds (20 minutes), will likely allow your job to complete. And hopefully job completion will provide some insight into why it's taking so much longer on this specific day.

I think the setting is a little confusing, as discussed another question here on the site: Linked server connections to Multi-subnet failover cluster

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.