0

One of our production SQL Servers the database is performing poorly. On the application side, they are experiencing this error:

An error occurred while updating the entries. See the inner exception for details.;
[INNER] Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.;
[INNER] The wait operation timed out;

These are the followings things we have tried to troubleshoot:

  1. checked if there any locking and blocking going on- there is no major blocking going on. If there is blocking going on, resources are blocked for less than 1 min and against a different DB.

  2. captured queries using profiler. we noticed that insert, update and select are occurring simultaneously. so we are assuming there might be internal locking going on somewhere, but can't figure out where.

  3. there are no fragmentation present.

  4. there are no errors produced in the SQL server error logs.

  5. captured performance counters for a week and nothing critical was shown.

Has anyone ever experienced these errors before? If so, what did you do to troubleshoot and resolve the errors?

0

2 Answers 2

3

By default, the .Net client has a 30-second client-side command timeout. The error you are seeing is saying that the response from the server took more than 30 seconds.

Here's some example C# code showing the problem by executing a WAITFOR DELAY statement that waits 31 seconds:

using System;
using System.Data.SqlClient;

namespace ConnectionTimeoutTest
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnectionStringBuilder sqlConnectionStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder
            {
                InitialCatalog = "tempdb",
                IntegratedSecurity = true,
                DataSource = "localhost"
            };

            using SqlConnection connection = new SqlConnection();
            connection.ConnectionString = sqlConnectionStringBuilder.ConnectionString;
            connection.Open();
            using SqlCommand sqlCommand = new SqlCommand("WAITFOR DELAY '00:00:31';SELECT 'Hello World!';", connection);
            SqlDataReader reader = sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader.GetString(0));
            };
        }
    }
}

And the exception:

System.Data.SqlClient.SqlException
HResult=0x80131904
Message=Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Source=Core .Net SqlClient Data Provider

StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at ConnectionTimeoutTest.Program.Main(String[] args) in C:\Users\Hannah.MVCT\source\repos\ConnectionTimeoutTest\ConnectionTimeoutTest\Program.cs:line 23

Inner Exception 1:
Win32Exception: The wait operation timed out.

Here's the fix to make the client wait for 60 seconds:

using System;
using System.Data.SqlClient;

namespace ConnectionTimeoutTest
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnectionStringBuilder sqlConnectionStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder
            {
                InitialCatalog = "tempdb",
                IntegratedSecurity = true,
                DataSource = "localhost"
            };

            using SqlConnection connection = new SqlConnection();
            connection.ConnectionString = sqlConnectionStringBuilder.ConnectionString;
            connection.Open();
            using SqlCommand sqlCommand = new SqlCommand("WAITFOR DELAY '00:00:31';SELECT 'Hello World!';", connection);
            sqlCommand.CommandTimeout = 60;  // <---- this is new
            SqlDataReader reader = sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader.GetString(0));
            };
        }
    }
}

The code above is available in GitHub.

0
0

First steps you can take to troubleshoot your error:

  1. Try to increase Connection Timeout property in the connection string from default 15 (or 30) to 60 or more seconds

  2. Try to increase CommandTimeout property of the SqlCommand object (Hannah's answer has great detail on how to do it).

Up until very recently, the .NET System.Data.SqlClient driver allowed setting the CommandTimeout only via the SqlCommand object. Note that In the newer Microsoft.Data.SqlClient driver, specifically v2.1, you can now set this via the connection string, and it will be applied to any SqlCommand associated with that connection automatically

If #1 reduces or eradicates the error, then you've had a connectivity error
If #2 helps (more likely than #1), then you are having long-running query problem (over 30 secs) and you have to dig deeper to understand what creates a slowness in a query

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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