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.
