TinyTds Error: Adaptive Server connection timed out

Question

We are running a ruby on rails application on rails 3.2.12 (ruby 1.9.3) with current tinyTDS gem 0.6.2.

We use MS SQL 2012 or 2014 and facing more then usual the following error message:

TinyTds::Error: Adaptive Server connection timed out: EXEC sp_executesql [...]

Database AUTOCLOSE is off. TCP Socket Timeouts are default Windows system.

Application server is on machine #1 (windows server), SQL server is on machine #2 (windows server).

When I check the connections (netstat) I have like 250 connections open for around 20-30 users. I run perform.exe to see idle time on SQL server for the data and log disks.

database.yml has connection pool:32 and reconnect:true.

To me it looks like that tinyTDS lost connection and any exception prevents from reconnecting.

The question is, how can I debug into the problem to find out what the problem is?

UPDATE

My mistake, the original error message belongs to tinytDS 0.5.x. Since I updated to the latest version I get the following error in addition or instead:

ActiveRecord::LostConnection (TinyTds::Error: DBPROCESS is dead or not enabled: BEGIN TRANSACTION):

Factoring

Also, what version of FreeTDS have you compiled against? – Amand

For example running "tsql -C" will show what the system has in most cases. – Amand

I did not install tinyTDS myself, I used "gem install tinytds" on windows. – Factoring

"tsql -C" is a command from what? and you run this on application or database server? – Factoring

Ah, so if you are using the pre-built windows binary gem, then FreeTDS is 0.91 which is the latest stable, which is also good. I have seen issue where unix systems compile TinyTDS to FreeTDS v0.82 and has caused issues like that before. – Amand

Answer 1

First, that pool size seems excessive. Are you using a ton of threads? If not, then only one connection will be used per app request/response. Just seems like that value is way to high.

Second, what SQL timed out? Have you found that certain SQL is slower than others? If so, then you have two options. The first would be to tune the DB using standard practices like indexes, etc. The second would be to increase the "timeout" option in your database.yml. The default timeout is 5000 which is 5 seconds. Have you tried setting it to 10000? I guess what I am asking is how are you sure this is a "connect" timeout vs a "wait" timeout?

Amand

We use thin server and there we run two of them, one on port 3101 nd one on port 3102, front end is a apache which handle the load balancer. We had some issues where the production.log adviced us to increase connection pool, that's why is that high. About the connection timeouts: There is no special sql that create the connection timeout, very simple or complex ones, it just happen sometimes that we have timeouts, connection, not query timesouts. Timeoutset is set to 10000 already. – Factoring

I think there was an issue in the adapter where normal timeouts (they happen) caused the adapter to crash. I'm sorry, I have not spent much time in the adapter lately, so I do not know off hand. – Amand

Is there anyway I can find out or anyone who can? – Factoring

And how I can find out why the adapter has DEADLOCK or TIMEOUT at all? There must be a reason lately... – Factoring

Hey I'm just wondering if a solution was reached here on this issue? We are having the EXACT same problem at my company for our application. Different circumstances (we are using Rufus Scheduler to simulate multiple threads), but the same problems happen (DBPROCESS IS DEAD with the same error message). – Hefner

@BillyM. we recently updated to Rails 4, ruby 2.0.0 and latest TineTDS version. We still face some DB Errors sometimes but at least our ruby task do not crash anymore totally – Factoring