Tjyylli

Co-worker works way more than he should

With indentation set to `0em`, when using a line break, there is still an indentation of a size of a space

Do I need to protect SFP ports and optics from dust/contaminants? If so, how?

Is a 5 watt UHF/VHF handheld considered QRP?

Is Bran literally the world's memory?

Does Feeblemind produce an ongoing magical effect that can be dispelled?

My admission is revoked after accepting the admission offer

Are all CP/M-80 implementations binary compatible?

Raising a bilingual kid. When should we introduce the majority language?

Office 365 Outlook has huge fonts - how to make smaller?

How do I check if a string is entirely made of the same substring?

Are these square matrices always diagonalisable?

Suing a Police Officer Instead of the Police Department

France's Public Holidays' Puzzle

Has a Nobel Peace laureate ever been accused of war crimes?

Arriving in Atlanta after US Preclearance in Dublin. Will I go through TSA security in Atlanta to transfer to a connecting flight?

What do you call the part of a novel that is not dialog?

Trumpet valves, lengths, and pitch

How can I make a line end at the edge of an irregular shape?

How to get even lighting when using flash for group photos near wall?

What is this word supposed to be?

Protagonist's race is hidden - should I reveal it?

Second order approximation of the loss function (Deep learning book, 7.33)

What is a 'Key' in computer science?

Linked server returned message “Query timeout expired.”

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

1

0

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 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?

sql-server linked-server etl query-timeout

share|improve this question

edited 11 hours ago

Paul White♦

54.3k14288461

asked 14 hours ago

LeonLeon

62

New contributor

Leon is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Is there any scheduled job which runs every 11th day when this job fails? have you checked the linked server setting for timeout and remote query timeout?
  
  – Learning_DBAdmin
  17 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Have you checked both servers for anything that runs on that 11th day at that time? It could be the source, or destination server. Did you see anything else in the sql server logs on either instance?
  
  – scsimon
  13 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is the "remote query timeout" set to 0? Why don't you try using OPENROWSET instead of linked server? Linked servers won't be able to use any indexes from your source query.
  
  – Dan
  13 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @scsimon 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
  13 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Dan yes the remote query timeout is set to 0. We use linked server because the ETL procedures are automatically generated by a BI-Tool names Bigenius. But i think it would be possible to change it to OPENROWSET, but it will cost a lot of time, because there are many complex stored procedures.
  
  – Leon
  13 hours ago
  
  
  

  
  
  
  

add a comment | 

1

0

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 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?

sql-server linked-server etl query-timeout

share|improve this question

edited 11 hours ago

Paul White♦

54.3k14288461

asked 14 hours ago

LeonLeon

62

New contributor

Leon is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Is there any scheduled job which runs every 11th day when this job fails? have you checked the linked server setting for timeout and remote query timeout?
  
  – Learning_DBAdmin
  17 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Have you checked both servers for anything that runs on that 11th day at that time? It could be the source, or destination server. Did you see anything else in the sql server logs on either instance?
  
  – scsimon
  13 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is the "remote query timeout" set to 0? Why don't you try using OPENROWSET instead of linked server? Linked servers won't be able to use any indexes from your source query.
  
  – Dan
  13 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @scsimon 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
  13 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Dan yes the remote query timeout is set to 0. We use linked server because the ETL procedures are automatically generated by a BI-Tool names Bigenius. But i think it would be possible to change it to OPENROWSET, but it will cost a lot of time, because there are many complex stored procedures.
  
  – Leon
  13 hours ago
  
  
  

  
  
  
  

add a comment | 

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 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?

sql-server linked-server etl query-timeout

share|improve this question

edited 11 hours ago

Paul White♦

54.3k14288461

asked 14 hours ago

LeonLeon

62

New contributor

Leon is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 11 hours ago

Paul White♦

54.3k14288461

asked 14 hours ago

LeonLeon

62

New contributor

Leon is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 11 hours ago

Paul White♦

54.3k14288461

asked 14 hours ago

LeonLeon

62

New contributor

Leon is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited 11 hours ago

Paul White♦

54.3k14288461

edited 11 hours ago

Paul White♦

54.3k14288461

asked 14 hours ago

LeonLeon

62

New contributor

Leon is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 14 hours ago

LeonLeon

62

1 Answer
1

active

oldest

votes

2

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

share|improve this answer

edited 12 hours ago

answered 12 hours ago

Josh DarnellJosh Darnell

8,45922244

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for the approach, is there a way, that i can check the remote query timeout setting in the database with an sql query? No it is really an intervall of 11 days, we made an excel file of the job execution history and counted the days between the failures.
  
  – Leon
  12 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is a server setting (applied for all databases) sp_configure 'remote query timeout (s)'
  
  – Denis Rubashkin
  12 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Leon Thanks for clarifying the failure pattern, I'll remove that from my answer. The server-level remote query timeout setting (which Denis pointed out how to check) doesn't actually matter. If your linked server has a "query timeout" value of 0, then the timeout is 600 seconds (the default value for the server level setting - not the currently active value for the setting).
  
  – Josh Darnell
  12 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks guys for the information. But when i follow the link that @JoshDarnell added in the answer, the description there says: use sp_configure to set the value of the remote query timeout option to 0 to disable the time-out. so, when i set it to 0 it should be disabled, or why is it still 600 seconds?
  
  – Leon
  11 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Leon That's why I feel that it's confusing. The linked server "query timeout" option (set through the UI or by calling sp_serveroption) is not affected at all by the currently set value of the server level "remote query timeout" option (set by calling sp_configure). When the linked server option is set to 0, it just uses the same default value as the server level option (600 seconds).
  
  – Josh Darnell
  11 hours ago
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Leon is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f236584%2flinked-server-returned-message-query-timeout-expired%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

2

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

share|improve this answer

edited 12 hours ago

answered 12 hours ago

Josh DarnellJosh Darnell

8,45922244

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for the approach, is there a way, that i can check the remote query timeout setting in the database with an sql query? No it is really an intervall of 11 days, we made an excel file of the job execution history and counted the days between the failures.
  
  – Leon
  12 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is a server setting (applied for all databases) sp_configure 'remote query timeout (s)'
  
  – Denis Rubashkin
  12 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Leon Thanks for clarifying the failure pattern, I'll remove that from my answer. The server-level remote query timeout setting (which Denis pointed out how to check) doesn't actually matter. If your linked server has a "query timeout" value of 0, then the timeout is 600 seconds (the default value for the server level setting - not the currently active value for the setting).
  
  – Josh Darnell
  12 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks guys for the information. But when i follow the link that @JoshDarnell added in the answer, the description there says: use sp_configure to set the value of the remote query timeout option to 0 to disable the time-out. so, when i set it to 0 it should be disabled, or why is it still 600 seconds?
  
  – Leon
  11 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Leon That's why I feel that it's confusing. The linked server "query timeout" option (set through the UI or by calling sp_serveroption) is not affected at all by the currently set value of the server level "remote query timeout" option (set by calling sp_configure). When the linked server option is set to 0, it just uses the same default value as the server level option (600 seconds).
  
  – Josh Darnell
  11 hours ago
  

  
  
  
  

add a comment | 

2

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

share|improve this answer

edited 12 hours ago

answered 12 hours ago

Josh DarnellJosh Darnell

8,45922244

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for the approach, is there a way, that i can check the remote query timeout setting in the database with an sql query? No it is really an intervall of 11 days, we made an excel file of the job execution history and counted the days between the failures.
  
  – Leon
  12 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is a server setting (applied for all databases) sp_configure 'remote query timeout (s)'
  
  – Denis Rubashkin
  12 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Leon Thanks for clarifying the failure pattern, I'll remove that from my answer. The server-level remote query timeout setting (which Denis pointed out how to check) doesn't actually matter. If your linked server has a "query timeout" value of 0, then the timeout is 600 seconds (the default value for the server level setting - not the currently active value for the setting).
  
  – Josh Darnell
  12 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks guys for the information. But when i follow the link that @JoshDarnell added in the answer, the description there says: use sp_configure to set the value of the remote query timeout option to 0 to disable the time-out. so, when i set it to 0 it should be disabled, or why is it still 600 seconds?
  
  – Leon
  11 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Leon That's why I feel that it's confusing. The linked server "query timeout" option (set through the UI or by calling sp_serveroption) is not affected at all by the currently set value of the server level "remote query timeout" option (set by calling sp_configure). When the linked server option is set to 0, it just uses the same default value as the server level option (600 seconds).
  
  – Josh Darnell
  11 hours ago
  

  
  
  
  

add a comment | 

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

share|improve this answer

edited 12 hours ago

answered 12 hours ago

Josh DarnellJosh Darnell

8,45922244

share|improve this answer

edited 12 hours ago

answered 12 hours ago

Josh DarnellJosh Darnell

8,45922244

answered 12 hours ago

Josh DarnellJosh Darnell

8,45922244

answered 12 hours ago

Josh DarnellJosh Darnell

8,45922244