Getting Connection Reset Here

[jwbeaty]

Our system is now getting this message: Error Executing Database
Query.[Macromedia][SQLServer JDBC Driver]A problem occurred when attempting to
contact the server (Server returned: Connection reset). Please ensure that the
server parameters passed to the driver are correct and that the server is
running. Also ensure that the maximum number of connections have not been
exceeded for this server.

We have verified that our SQL server is not getting max connections (it is set
to unlimited connections). We also have tested connectivity using a variety of
tools and can easily reach 250. We normally only use about 50-60 connection to
SQL via retained connections from CF.

For all Cold Fusion experts, can anyone please discuss the best way to track
down this problem. What has worked in the past for you in order to eliminate
this issue? DSN parameter settings? Modification to SQL Queries? We have
just started seeing this problem and are mystified as to why we are getting it.
We are currently on MX7. We have checked out firewall and everything looks
good there so it is something else.

[Parr8head]

We are having the exact same problem where I work...so similar, in fact, that
I'm wondering if the poster is someone who works here... We are stumped as
well.

It probably should be noted that our database (SQL Server 2000) and web server
(IIS 6, MX7) are separate, both running on Windows 2003 SP1

This error makes an appearance at least once per day, averaging around 3 - 4
times per day. Mostly happens late at night/early morning, but it has occurred
at various times. We have a page that hits the server every 10 minutes. It
runs one insert query... This page generates the error most often (possibly
because it hits the server most often), but it's happened on multiple, not
necessarily query-intensive pages... We have not been able to recreate the
error. I have not been able to find any helpful info regarding this error so
any insight would be greatly appreciated!

Thanks in advance.

[jc004d]

Same problem. I have 4 CF servers on a lan with a dedicated SQL Server,
unlimited connections. Only one server returns this error, and it always
returns it at least once a day. Yesterday it returned it over 100 times, only
loading page requests about 50% of the time.

The sql server was not even under load, nonetheless out of connections. Other
CF servers hit this DB, none return this error.

This particular server is approaching unusable due to this error, and this is
nothing new. I've received this error for what seems like years now in various
forms, and I've never seen or received a real answer from macromedia about what
causes it (it used to be 'connection reset by peer', IIRC).

As is, I have to hold the servers hand, and be up with it at 1am every morning
as it runs back-end processing jobs from 1am - 10am.

The difference between this server and my other servers? This server is
almost entirely automated, running off a series of scheduled tasks. Some pages
run a few select queries and run for 5 seconds, some pages run hundreds of
queries and last upwards of 40 minutes (the template execution, not the
queries).

I have one template that can reliably return this error every single time it's
run. It does something like this (simplified).
-------
-query to datasource1, returning about 100,000 records) (under a minute)
-loop through resultset, checking for conditions. On condition, run secondary
queries on datasource2 to fix condition (takes anywhere from 10-40 minutes)
-query back to datasource1 to report success of operation (under a minute)
--------
that second query to datasource1 will without fail return this error, but this
is far from the only place I receive it. I tried maintaining the connection
and adjusting the timeout value to over 40 minutes, didn't fix it. I tried
unmaintaining connections and timing out in 5 minutes, didn't fix it.

Is there a proper JDBC setting for long running queries and jobs? Is there
any real documentation as to what all the JDBC settings actually do, and how
changing them actually impacts query performance (besides the sentence that
each setting gets in liveDocs)? Is there any recognition that this 'connection
reset' is a wide spread error that is slowly driving experienced CF developers
insane?

Thanks

[tsongas]

I also started having this problem after upgrading from 6.1 to 7.0.1. Has
anyone found a solution? I have multiple shared servers, and sites on each
server now occasionally generate this error.

I am suspicious of one site that has multiple queries wrapped in
<cftransaction> tags as the source of this problem, is anyone else's code doing
that? Again this never happened with 6.1, so not sure where is the source of
the problem.

[Ginney]

Have you found a solution?

We have the same problem and thus far I have found no answers. I can recreate
the problem - it has to do with inactivity. A user will go to a dynamic page,
wait 5 minutes, and try to drill down. The error will occur. I can
reproduce this time after time, but I can not prevent it.