System.Data.SqlClient.SqlException : Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding

In this blog, we will discuss about the root cause of execution timeout reasons and how to fix the SQL Exception - Execution Timeout Expired

Recently I was dealing with huge data approximate 160M records and performing data analyzing task and on some point, I encountered SQL Exception.

System.Data.SqlClient.SqlException

  HResult=0x80131904

  Message=Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

  Source=.Net SqlClient Data Provider

  StackTrace:

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

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

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

Usually default command timeout is 30 seconds and if you don’t receive anything from database within 30 seconds, it will throw System.Data.SqlClient.SqlException : Execution Timeout Expired.

To resolve this problem, we need to increase the connection timeout for SQL command, if you feels SQL script /proc is already well tuned and tables are proper indexed.

Eg. :

SqlCommand command = new SqlCommand(commandText, sqlConnection);

command.CommandTimeout = 60000;

You can simply set waiting time for SQLCommand

There are two timeout options available for application to access SQL Server Database :

1. Connection Timeout
2. Command Timeout

1. Connection Timeout: it is waiting time for application to establish connection with Database and if application is not able to establish connection with database, it throws SQL Exception

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified).

You can increase the waiting time for SQL connection timeout and you have to add connection timeout attribute in SQL connection string

string connectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;Connection Timeout=30";

2. Command Timeout – it is waiting time for command to execute script, if within command timeout it does not complete execution, it throws SQL Exception

System.Data.SqlClient.SqlException : Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding

And by default, command timeout value is 30 seconds and you can increase the waiting time for command timeout.

SqlCommand command = new SqlCommand(commandText, sqlConnection);

command.CommandTimeout = 6000; (values in millisecond)

Thanks for visiting!!