Wednesday, August 27, 2008

SqlServer Timeout Expired Exception

One of our users emailed and said he got the following exception with a reporting query:
System.Data.SqlClient.SqlException: Timeout expired.  
The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

The exception just started happening because the database has slowly been increasing to where the report query fails to finish in a timely manner. The timely manner is 30 seconds, the default for .Net database connections.

You can increase the default time of 30 seconds by using the "CommandTimeout" field on the "Command" object as shown below:

using (connection) {
SqlCommand sqlcommand = connection.CreateCommand();
sqlcommand.CommandTimeout = 60; //default is 30 seconds
sqlcommand.CommandText = sqlText;

Don't be misled by the database connection string option, "Connection Timeout", e.g., "Data Source=myMachine; Integrated Security=SSPI; database=Northwind;Connection Timeout=60". That timeout is for getting the connection, not for how long it queries the database.

1 comment:

Anonymous said...

You could also specify it in the connection string with ";Default Command Timeout=180". Kind of helps if you do not want to recompile your entire project.