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?)

thanks

 

[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.

hth

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