Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

Question

I am using a stored procedure to retrieve a record and display it in a grid.

The first time around, it will throw an exception:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Stack trace

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
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)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()

I used

SELECT * 
FROM sys.dm_tran_session_transactions

to check how many active transactions are currently in my database and I found none.

For the second time onwards this stored procedure run perfectly (will not throw the timeout exception). What may be the cause of this behavior?

possible duplicate of Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding — tom redfern
Possible duplicate of Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated — Ehsan Sajjad

DeanOC DeanOC · Accepted Answer · 2012-07-26T09:55:49

If your stored proc is complex, or your database design is poor (e.g. lack of indexes) then SQL Server may be having trouble coming up with the optimal execution plan and may not be able to evaluate all the options So it will evaluate as many as it can and will then pick something. The time taken may exceed your timeout limit.

The second time around, you are executing the same stored proc so SQL Server can keep all the work it did the first time, and will be able to finish evaluating its options and give you back the data before you get another time out.

I have a proc just like this. If reboot my server, the first time I run the proc I always get a timeout, but the second time it always works.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

1 Answers