ColdFusion 8 / SQL Server 2008 Connection Issue

[wmkolcz]

I have seen this issue all over Google but its never for my combo of servers, I am running ColdFusion 8 and SQL Server 2008 and I keep getting this error:

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

Does anyone know how to actually fix this? My new application is about to go live next week and this is stopping it.

[Adam Cameron.]

Have you ever been able to connect to the DB in question with the DSN config you have?  IE: has this just started happening, or are you setting up a new DSN and it doesn't work?  Or does it happen sometimes but not all the time?  Does the DSN work fine for a while, then start giving this error then continue to give the error until you restart CF or something?

Have you done what it says: ensured the maximum number of connections hasn't been exceded?

--

Adam

[wmkolcz]

I used to work fine. Then this started creeping up. It comes and goes. I restarted ColdFusion, I restarted my MS SQL Server, and I rebooted my Cloud Server. It helps, but then it creeps back up again.

I dont see how the connections are maxed since the SQL Server has no limit. I would be game to check, but have no idea how. Also, this site has limited usage right now except for a few beta testers.

[carl type3]

Hi,

Is CF and SQL on same server or separate? If separate maybe run a PING to check connectivity is not broken somehow.

Maximum number of connections is usually zero ie unlimited. Use SQL Management Studio to check.

When you get the "[Macromedia][SQLServer  JDBC Driver]A problem occurred when attempting to contact the server  (Server returned: Connection reset)" does anything get logged in CF\runtime\logs\OUT.log (JRun4\logs\ in case multi-server)?

Does network packet size match between your CF datasource and SQL settings? eg:

CFadmin UI > Data & Services > Datasources > Microsoft SQL Server

SQL Management Studio

HTH, Carl.

[Adam Cameron.]

Hmmm.  Along with the error msg, it should be saying which template was running at the time.  Is it always the same template?  Can you tie it down to a specific query?  Is it a subset of templates? Seemingly any template that has a query on it (working out whether it's "a subset" or "any" might require some investigation).

From my reading, it sounds like this is often caused by network-layer interruptions.  If you run a ping between the CF server and the DB server, what are the response times like?  Keep ping running for a while: do you see any drop-offs?

That said, that the thing seems to run fine for a while and then starts happening suggests it's not network related; more application-related.  When you restatr CF and/or SQL, do you make sure to do one or the other rather than btoh, so see whether one or the other influences behaviour more than the other?

Are you finding your requests are taking longer and longer around when this starts happening?

What's your memory usage doing during this?

Have a look at the queries you're running.  How many are you making per request, in general?  Do you have any templates that run queries that return large record sets, or take a while to run, on a regular basis?  Given you said you've only got a few testers on the box, it's unlikely to be this sort of issue though.

What happens when you load-test the app?  Do you start seeing these symptoms faster than under just testing load?

--

Adam

[wmkolcz]

There isn't one query that is triggering the issue. There isn't one process that is triggering the issue. It really is random. I see, from past posts, that it is an issue with CF and SQL Server. Doesn't happen on my test enviorment which is set up pretty much the same way as my prod..other than I am running CF9 on the test enviorment.

Both CF and SQL Server on are on the same server (Windows Server 2008, Rackspace Cloud). The connection seems fine. The datasource checks out good.

[Adam Cameron.]

OK there's a few more questions I asked there which are still relevant.

Also are you using a domain name or an IP address to connect to the DB?  I've seen DNS resolution delays causing this sort of thing in the past.

Contrary to what you read, I severely doubt it's "an issue with CF and SQL Server".  Because if it was, a lot more people would be havnig the issue.  Now if you said it was the way you had one, the other, or both configured... yeah, that sound more likely 😉

--

Adam

[carl type3]

Hi,

Works ok in test environment but not production can be a load induced problem. Still keen to know if there are any messages in "coldfusion-out.log" when database stops connecting and prior?

Indeed restart either CF or SQL not both at once - to get a better idea of which one is having a problem. You can only be wrong and still be down for a moment longer if you guess wrong and restart the one that is not broken then need to restart the other.

How DNS resolves the datasource "server" details can have an influence as noted.

HTH, Carl

.