Timeout expired - Server not responding - OPC/C#/SQL Server

[Guest]

I recently inherited support for a C# application that communicates with SQL
Server and some OPC devices. Unfortunately, I don't have access to the
required version of the source code (working on this aspect), so I'm having
problems troubleshooting the following problem:

The application intermittently returns this error message "Timeout expired.
The timeout period elapsed prior to completion of the operation or the server
is not responding." for the System.Data.SqlClient.SqlCommand.ExecuteReader
and ExecuteNonQuery methods.

This error occurred about two weeks ago, and although I performed a variety
of modifications, the ones that seemed to restore the application's stability
included freeing up some hard drive space, backing up the SQL server
database, and clearing up some OPC configuration problems (not sure if the
restored functionality was coincidence???). The application then operated as
desired for approximately two weeks when suddenly it started timing out again
(according to support techs, nothing has changed - the application just
started returning these errors out of the blue).

Typical operation is to collect data from the OPC devices and store it in
the SQL Server database every 1-5 seconds (depending on configured polling
rate) and the amount of data being transferred isn't extremely high.
Communication to the OPC devices appears to be stable (confirmed through
third-party OPC client testing), and when the errors occur, they occur
approximately every other polling cycle.

I've done some preliminary research on the internet, but there seem to be
many variations of this problem. BizTalk, I believe, even has a .dll to
address this type of problem. Does anyone have any ideas on some things to
try? I realize that this may be hard to track down for a variety of reasons,
but I'm open to suggestions.

The application server is running W2K SP4 with SQL Server 2000 and .NET
framework 1.0.

Thank you,

Curt

 

[Andrew J. Kelly]

You should run profiler to see if you can tell what is actually happening
when the timeouts occur. Without that there isn't much to go on. With that
said there are a few things that you can check on. Make sure you don't have
AutoShrink for the DB turned on or a job that shrinks the db or log files.
Also make sure there is plenty of free space in the database. If not an it
has to autogrow it can take longer than the timeout period and cause these
types of issues. You can run sp_who2 to see if you have blocking issues
when this occurs as well. Also perfmon can go a long way towards seeing if
the systems resources (cpu, disk, memory etc) are having issues during this
time.

 

[Guest]

Thanks, Andrew. The database tables the C# application was hitting had grown
much larger than I had anticipated (inexperience with the application). Once
I deleted most of the records, everything was kosher.

As usual, now that the problem is fixed, the timeout makes sense.

Thank you!

Curt