SQL Timeout Errors - Can Anyone Help?

[alvinstraight38@hotmail.com]

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.Sql Client.SqlExcep tion: Timeout
expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.

at System.Data.Sql Client.SqlComma nd.ExecuteReade r(CommandBehavi or
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)

at System.Data.Sql Client.SqlComma nd.ExecuteNonQu ery()


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?

[Erland Sommarskog]

Re: SQL Timeout Errors - Can Anyone Help?

alvinstraight38 @hotmail.com (alvinstraight3 8@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.Sql Client.SqlExcep tion: Timeout
expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
>
at System.Data.Sql Client.SqlComma nd.ExecuteReade r(CommandBehavi or
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
>
at System.Data.Sql Client.SqlComma nd.ExecuteNonQu ery()
>
>
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@sommarsk og.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