TinyTds Error: Adaptive Server connection timed out

ruby-on-rails ruby sql-server tiny-tds
YvesR picture YvesR · Dec 5, 2014 · Viewed 10.3k times · Source

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):

Answer

MetaSkills picture MetaSkills · Dec 5, 2014

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?

Related questions

How to get a random number in Ruby

How do I generate a random number between 0 and n?

Read More
How do I get the current absolute URL in Ruby on Rails?

How can I get the current absolute URL in my Ruby on Rails view? The request.request_uri only returns the relative URL.

Read More
How to remove a key from Hash and get the remaining hash in Ruby/Rails?

To add a new pair to Hash I do: {:a => 1, :b => 2}.merge!({:c => 3}) #=> {:a => 1, :b => 2, :c => 3} Is there a similar way to delete a key from Hash ? This works: {:a => 1, :b => 2}.…

Read More