Hello everyone, I'm encountering an issue with a web backend application that uses SQL Server 2019. We send a request to the UPS API, but it fails, and the application error log shows the following message: LOGGER Error: 0 : 6/14/2024 2:45:38 PM : System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back. Operation cancelled by user. The statement has been terminated. The statement has been terminated. The statement has been terminated. The statement has been terminated. The statement has been terminated. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) Environment Details: SQL Server 2019 Web backend application in C# Connecting to UPS API Troubleshooting Steps Taken: Verified there are no SQL connection dropouts, blocks, or locks. Checked for network latency issues. Ensured SQL queries are optimized, and indexes are properly set. Despite these measures, the error persists. Here are some additional details that might help: Query Complexity: The query involved in the UPS API request is not overly complex, but it does join several tables and includes a few WHERE clauses. Resource Utilization: Monitoring indicates no excessive CPU or memory usage on the SQL Server. Timeout Settings: It's already Unlimited Questions: Are there any specific settings in SQL Server 2019 or .NET's SqlClient that could help prevent these timeout issues? Could the use of MARS (Multiple Active Result Sets) be causing these problems? If so, how can I mitigate this? What additional steps can I take to diagnose and resolve this timeout error? Any advice or pointers would be greatly appreciated. Thank you!

Hi @ Vinith , Thank you for reaching out and welcome to Microsoft Q&A. You've tried to increase the connection timeout, but it was still this exception. I found this article and it was found that incorrect caching of statistics and/or query plans for the database could also cause this issue. Could you please try this query: exec sp_updatestats Updating statistics ensures that queries are compiled with the latest statistics. If the problem is not solved after the execution, please try this: dbcc freeproccache Deletes all elements in the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or deletes all cache entries associated with a specified resource pool. It can be determined that the issue appears because the execution plan of the in-memory cache is wrong or not optimal. This is the official document , hope this can help you well. Feel free to share your issue here if you have any confusions. Best regards, Lucy Chen If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread. <a href="https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fanswers%2Fsupport%2Femail-notifications&data=05%7C02%7Cv-xinc%40microsoft.com%7C9b8c827cb42642ce852408dc43e66a2a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C638459904116612195%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=Enb7k6yaPROUCn4EQgXIrCLX2qXwUpzmasNuBLy%2BznA%3D&reserved=0" rel="ugc nofollow">https://docs.microsoft.com/en-us/answers/support/email-notifications

Execution Timeout Expired Error with UPS API Request in SQL Server 2019 Backend

Vinith 45

Hello everyone,

I'm encountering an issue with a web backend application that uses SQL Server 2019. We send a request to the UPS API, but it fails, and the application error log shows the following message:

LOGGER Error: 0 : 6/14/2024 2:45:38 PM : System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back.

Operation cancelled by user.

The statement has been terminated.

The statement has been terminated. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

Environment Details:

SQL Server 2019
Web backend application in C#
Connecting to UPS API

Troubleshooting Steps Taken:

Verified there are no SQL connection dropouts, blocks, or locks.
Checked for network latency issues.
Ensured SQL queries are optimized, and indexes are properly set.

Despite these measures, the error persists. Here are some additional details that might help:

Query Complexity: The query involved in the UPS API request is not overly complex, but it does join several tables and includes a few WHERE clauses.
Resource Utilization: Monitoring indicates no excessive CPU or memory usage on the SQL Server.
Timeout Settings: It's already Unlimited

Questions:

Are there any specific settings in SQL Server 2019 or .NET's SqlClient that could help prevent these timeout issues?
Could the use of MARS (Multiple Active Result Sets) be causing these problems? If so, how can I mitigate this?
What additional steps can I take to diagnose and resolve this timeout error?

Any advice or pointers would be greatly appreciated. Thank you!

Vinith 45

Hello all,

Thanks for looking and revert, so we had observed same time: 6/14/2024 2:45:38 PMin error log that below error:

UI Application Error:

LOGGER Error: 0 : 6/14/2024 2:45:38 PM : System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back.
Operation cancelled by user.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

SQL Error log:

2024-06-14 14:45:38.51 spid65      Autogrow of file 'DatabaseName_DATA' in database 'DatabaseName' was cancelled by user or timed out after 20592 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

here is our Database Auto grow settings:

Database_Properties Our Database Auto grow Settings is: by 5120MB, Unlimited,

is that the cause?

How do we prevent in future? can you please help here.

LucyChenMSFT-4874 2,825 Reputation points

2024-06-25T08:26:42.8433333+00:00

Hi @Vinith,

Is this issue solved now? If you have any questions, please feel free to share with us.

May I humbly suggest that you accept Answer if they helped. It may help others who see the thread in seek of help.

Your action would be helpful to other users who encounter the same issue and read this thread. Thank you for your understanding! :)

Thanks for your understanding. Wish you have a nice day!

Best regards,

Lucy Chen

Accepted answer

LucyChenMSFT-4874 2,825 Reputation points

2024-06-24T01:56:38.4833333+00:00
Hi @Vinith,

Thank you for reaching out and welcome to Microsoft Q&A.

You've tried to increase the connection timeout, but it was still this exception. I found this article and it was found that incorrect caching of statistics and/or query plans for the database could also cause this issue. Could you please try this query:

exec sp_updatestats

Updating statistics ensures that queries are compiled with the latest statistics.

If the problem is not solved after the execution, please try this:

dbcc freeproccache

Deletes all elements in the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or deletes all cache entries associated with a specified resource pool.

It can be determined that the issue appears because the execution plan of the in-memory cache is wrong or not optimal. This is the official document, hope this can help you well.

Feel free to share your issue here if you have any confusions.

Best regards,

Lucy Chen

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications
Please sign in to rate this answer.
Vinith 45 Reputation points

2024-06-25T06:23:17.12+00:00

Hi @LucyChenMSFT-4874 ,

Thanks for looking and revert, so we had observed same time: 6/14/2024 2:45:38 PM in error log that below error:

UI Application Error:

LOGGER Error: 0 : 6/14/2024 2:45:38 PM : System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back. Operation cancelled by user. The statement has been terminated. The statement has been terminated. The statement has been terminated. The statement has been terminated. The statement has been terminated. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

SQL Error log:

2024-06-14 14:45:38.51 spid65 Autogrow of file 'DatabaseName_DATA' in database 'DatabaseName' was cancelled by user or timed out after 20592 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

here is our Database Auto grow settings:

Our Database Auto grow Settings is: by 5120MB, Unlimited,

is that the cause?

How do we prevent in future? can you please help here.

LucyChenMSFT-4874 2,825 Reputation points

2024-06-25T06:43:16.03+00:00

Hi @Vinith,

Thank you for your kindly feedback!

For the error message in UI Application, I think the link in my answer provides the method.

The error message in SQL:

2024-06-14 14:45:38.51 spid65 Autogrow of file 'DatabaseName_DATA' in database 'DatabaseName' was cancelled by user or timed out after 20592 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

This means the log file has an auto-increment operation, and the response time is relatively slow, so all such a record is made in the error log. You can take a look at the log file size of database and compare the growth mode to see its proportion.

You can try the methods below to resolve the issue:

Change the log auto-growth mode of the database.

Add a database log backup plan to back up the database regularly.

If the database log is too large, you also need to shrink the transaction log.

Here is a known issue like yours, please check it out. Hope this can help you well!

Feel free to share your issue here if you have any confusions.

Best regards,

Lucy Chen

Vinith 45 Reputation points

2024-06-25T07:21:00.3833333+00:00

Hi @LucyChenMSFT-4874 ,

Thanks for prompt response !

is there any recommendations from Microsoft Auto-growth settings Both Datafile and log file for the Production DB Server?

Thanks & Regards,

Vinith Ankam.

LucyChenMSFT-4874 2,825 Reputation points

2024-06-25T08:14:53.7933333+00:00

Hi @Vinith,

Thank you for your kindly feedback!

We can get the information from the official document:

For a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow.

You can use alerts or monitoring programs to monitor file sizes and grow files proactively. This helps you avoid fragmentation and permits you to shift these maintenance activities to non-peak hours.

Autoshrink and autogrow must be carefully evaluated by a trained Database Administrator (DBA); They must not be left unmanaged.

Your autogrow increment must be large enough to avoid the performance penalties listed in the previous section. The exact value to use in your configuration setting and the choice between a percentage growth and a specific MB size growth depends on many factors in your environment. A general rule of thumb you can use for testing is to set your autogrow setting to about one-eight the size of the file.

Turn on the \<MAXSIZE> setting for each file to prevent any one file from growing to a point where it uses up all available disk space.

Keep the size of your transactions as small as possible to prevent unplanned file growth.

In addition, I find the two useful articles, I would suggest us read the whole blogs. We can learn a lot form them.

https://techyaz.com/sql-server/performance-tuning/understanding-database-autogrowth-sql-server/

https://www.red-gate.com/simple-talk/databases/sql-server/database-administration-sql-server/sql-server-database-growth-and-autogrowth-settings/

Best regards,

Lucy Chen

Erland Sommarskog 105.8K Reputation points MVP

2024-06-25T21:10:18.13+00:00

In addition to Lucy's posts: The reason that autogrow takes such time is that SQL Server has to zero out the newly allocated space in the data file. That is 5 GB of zeroes in this case. That should not take 20 seconds on server with a good I/O subsystem. But if the disks are on a overtaxed SAN, that could explain the inferior performance.

There is a way to address this, though. You (or someone with Windows admin permissions) can grant the service account for SQL Server the permission Perform volume maintenance tasks. With this permission, SQL Server does not have to clear the allocated area for the data file, so the allocation is more or less instant. (This does not apply to log files, they still need to be zeroed out.) Restart SQL Server are the rights assignment.

LucyChenMSFT-4874 2,825 Reputation points

2024-06-28T06:45:13.6366667+00:00

Hi @Vinith,

Is this issue solved now? If you have any questions, please feel free to share with us.

May I humbly suggest that you accept Answer if they helped. It may help others who see the thread in seek of help.

Your action would be helpful to other users who encounter the same issue and read this thread. Thank you for your understanding! :)

Thanks for your understanding. Wish you have a nice day!

Best regards,

Lucy Chen
Sign in to comment

1 additional answer

Olaf Helper 42,761

Regarding 1): SQL Server don't terminate queries on it's own (beside deadlock victim), it's a clinet setting, see SqlCommand.CommandTimeout Property.; defualt is 30 seconds.

But that will only "hide" the problem and don't solve it. Better optimize table/index/query design.

Vinith 45

Hi @Olaf Helper ,

Thanks for looking and revert, so we had observed same time: 6/14/2024 2:45:38 PMin error log that below error:

UI Application Error:

LOGGER Error: 0 : 6/14/2024 2:45:38 PM : System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back.
Operation cancelled by user.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

SQL Error log:

2024-06-14 14:45:38.51 spid65      Autogrow of file 'DatabaseName_DATA' in database 'DatabaseName' was cancelled by user or timed out after 20592 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

here is our Database Auto grow settings:

Database_Properties

Our Database Auto grow Settings is: by 5120MB, Unlimited,

is that the cause?

How do we prevent in future? can you please help here.

Share via

Execution Timeout Expired Error with UPS API Request in SQL Server 2019 Backend

1 additional answer