Transport-level errors, how do I setup SQL Server to avoid them?

Question

I keep getting the following error (in the thousands) in production:

A transport-level error has occurred when sending the request to the server. 
(provider: TCP Provider, error: 0 - An existing connection was forcibly closed 
    by the remote host.)

I even have a 1 G + log file.

The customer isn't happy with the performance of the application.

I can't produce this error in Development,do so I am guessing that the 2 severs are setup differently.

Additional Information:

There is no attempt to make Development and Production servers the same.

Production:

System:
Microsoft Windows Server 2003
for Small Business Server
Service Pack 2


Computer:
Intel(R) Xeon(R) CPU
E5310 @1.60GHz
1.60 GHz, 3.00 GB of RAM
Physical Address Extension

I believe the SQL Server is the SQL 2005 bundled with SBS.

The application is a WinForm program running on user's desktop systems.

I can't get onto the Development Server, but I'm sure that it exceeds the Production stats. I know that the SQL Server is 2005 Standard.

I have also run the program against a local SQL 2005 Express and not gotten any problems (and attached copies of the files from production).

Answer 1

The only times I have seen this error have been when the client and server have become forcibly disconnected, either because of the SQL service restarting or the network link being down.

The only reason I can think that would cause this to be a regular problem on in production and not development (if the to environments are otherwise identical) could be that in production the client (or the DB access libraries they use) are holding connections open for longer.

With the users reporting performance issues, it could be that you are having concurrency issues causing the server to become far to busy and connections to therefore timeout. Have you done any analysis on the server's performance metrics (memory/swap usage, disk IO, CPU use, SQL server specific performance counters, ...) during times when performance it poor?

Could you provide more detail of the setup? Are the client application(s) and database server all on the same machine? What versions/editions of SQL Server are you using and what access method are you using?