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


scott mcfadden

We use ADO.NET / SQLProvider against SQL2000 for our web application. We
have started to see more and more of these errors:

Exception Type: System.Data.SqlClient.SqlException

Errors: System.Data.SqlClient.SqlErrorCollection

Class: 10

LineNumber: 0

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

Number: -2

Procedure: ConnectionRead (recv()).

From the error text, it appears that this error is more of a TCP/IP type
error. Is this a network issue rather than TSQL performance issue?

We use the default SqlCommand timeout of 30 seconds (transactions should
never take longer this). We use the TCP/IP network library for
SqlConnections. I have read that other folks have recommended using Named
Pipes sql network library as it has less problems and is faster than the
TCP/IP Sql network library. We use the default ado.net / sql connection
pooling values. We use Win2003 SP1 for the OS / web server and SQL 2000 SP4
as the SQLServer.

What are the standard things to check when encountering this type of error
(i.e. design of query, MDAC version, misssed SqlConnection / SqlDataReader
closes, Sql Network library, etc?)


William \(Bill\) Vaughn

This does not look like a Connection Pool issue--the message returned by the
pooling mechanism is different, but I would not discount this issue. Timeout
errors are traditionally caused when SQL Server is loaded beyond capacity. I
would turn on the Profiler (which can also adversely impact performance) and
see what the Server is being asked to do and how long it's taking to get the
job done. I expect you might find logical collisions (lock contentions) or
simply procedures (or queries) that are taking too long to execute.
Performance is most often a function of design, not implementation--it's not
the question that takes so long, it's the answer. SQL Server 2000 has a
number of tools you can use (SQL Server 2005 has even more) including the
query analyzer to figure out how efficiently your queries are being
executed. It's probably a matter of isolating the application that's causing
the problem. Of course, it might not be your applications at all--it could
be something the server is being asked to do in addition to executing your
queries. For example, you could be running Reporting Services, a print
server or a Castle Wolfenstein screen saver in the background.


William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question