How to increase Execution Timeout while running a T-SQL for custom metrics?
davidwatson
Posts: 3 New member
Hi ,
I was wondering if there is any way to increase execution timeout for T-SQL query?
What am I doing?
1) I am using Redgate Monitor installed in Windows server. Redgate Monitor 14.0.8.13822
2) I am creating a custom metric and alerts, running a T-SQL query to collect data.
3) Tested the T-SQL Query on a small database and it runs fine.
4) When running on a larger database, I keep getting the error below after 300,000ms (which is after 5 minutes):
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
I was wondering if there is any way to increase execution timeout for T-SQL query?
What am I doing?
1) I am using Redgate Monitor installed in Windows server. Redgate Monitor 14.0.8.13822
2) I am creating a custom metric and alerts, running a T-SQL query to collect data.
3) Tested the T-SQL Query on a small database and it runs fine.
4) When running on a larger database, I keep getting the error below after 300,000ms (which is after 5 minutes):
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Answers
In Redgate Monitor you can configure the connection and execution timeouts from the Configuration > Monitored servers page, by clicking on the three dots menu at the right of the row for the SQL Server instance in question
then choosing "Edit credentials"
and on the modal window that appears, choosing "Edit properties"
and on that modal, setting the option for Execution timeout as needed
However, as the above are the defaults (which mean don't timeout), it's likely the "Remote query timeout" from SQL Server itself is causing the problem, which you can find from SSMS by right-clicking on the instance and choosing properties at the bottom:
Then choosing "Connections" from the left sidebar
Kind regards,
Alex
Have you visited our Help Center?
Thank you for your answer.
I already tried these configurations but it did not work.
First, I tried from the host machine (redgate monitored servers).
Then, I tried the SQL Server instance itself (redgate monitored servers).
Then I tried both to have the same value, did not work.
Then, I tried on the SQL Server itself, the Remote query timeout configuration, also did not work.
Regards,
Dave
Ah, righto, I've dug a bit deeper into the code and it is actually using a hard coded value specifically for custom metrics. I'm checking with the dev team if there is a specific reason for this.
Kind regards,
Alex
Have you visited our Help Center?
I can see from your internal ticket you're going to rework your query to try and get it in under the 5 minute timeout.
Speaking to the team, since all of the custom metrics are run in a transaction, it seems this is the reason we have hard-coded a timeout, to avoid problems with very long running transactions in custom metrics impacting the target entities.
The team may revisit this in the future, but for now it is going to be left as is.
Kind regards,
Alex
Have you visited our Help Center?
Yes, we are reworking our query as of the moment.
I'll take note of the reason why its hard-coded and share with my team.
Thank you so much for all the help and effot.
Regards,
David