Posted by Erland Sommarskog on 10/03/07 21:55

alvinstraight38@hotmail.com (alvinstraight38@hotmail.com) writes:
> I have a client who has been receiving hundreds of SQL timeout error
> messages in their error logs. Specifically, the message looks like
> this:
>
> MESSAGE : 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.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
>
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
>
>
> The message just tells me that the update processed failed because SQL
> timed out. Yet other updates are getting through. I spent today
> deleting old records in her database tables thinking maybe the
> database had grown so large that it was taking too look to search the
> records, but it did not fix the problem. Any suggestions on how I can
> troubleshoot this problem futher?

SQL Server does not time out. It's the client that gets bored of waiting
for SQL Server to return a result set. Most client APIs have a default
timeout of 30 seconds. These timeout can cause a great mess if they
are not handled properly. All SQL Server knows is that the client says
"stop executing", so SQL Server stops executing. But no transaction is
rollback, and if the client does not issue a rollback, locks will continue
to pile up, and you get further blocking and timeout.

There are two possible reasons why a query takes more than 30 seconds
to run: 1) it is a slow query. 2) there is a blocking situation. In the
first case, an easy fix is to change the application and set the command
timeout to 0 (= wait forever) to permit SQL Server more time. Provided of
course, that the longer execution time is acceptable. In a blocking
situation, it depends on what the blocker is up to. If it is an idle
process that failed to commit or rollback a transaction, changing the
timeout is not going to help.

As for troubleshooting, you need of course find which queries that are
timing out. You also need to investigate whether there is blocking;
use sp_who2 for that. If the query is running slow in itself, and you
think it should run faster, you need to analyse the query and the
execution plan to see if the query can be tweaked, or if you need an
index.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]