"Adaptive Server connection timed out" with SQL Server 9.00.4053.00

[madprog]

Hello,

I'm trying to run rake db:migrate from my rails application to a SQL Server 2005 (9.00.4053.00). Several commands are run on the server, according to SQL Server Profiler:

set textsize 64512 
go
use MyDatabase
go
SET ANSI_DEFAULTS ON
go
SET CURSOR_CLOSE_ON_COMMIT OFF
go
SET IMPLICIT_TRANSACTIONS OFF
go
SELECT @@version
go
USE [MyDatabase]
go
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME <> 'dtproperties' AND TABLE_SCHEMA = schema_name()
go
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME <> 'dtproperties' AND TABLE_SCHEMA = schema_name()
go
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME <> 'dtproperties' AND TABLE_SCHEMA = schema_name()
go

I patched tiny_tds to add some printf traces in rb_tinytds_raise_error. From what I understand, the connection times out, and tiny_tds tries to sync with the server again with dbsqlok, but stays locked into this call for 15 minutes, before an other timeout occurs.

05/19/11t08:12:10 rb_tinytds_raise_error(0x01e8f580, 1, Adaptive Server connection timed out, error, 6, 20003, 0)
05/19/11t08:12:10 Calling dbsqlok
05/19/11t08:27:10 rb_tinytds_raise_error(0x01e8f580, 1, Read from the server failed, error, 9, 20004, 60)
05/19/11t08:27:10 Calling dbsqlok
05/19/11t08:27:10 Returned from dbsqlok
05/19/11t08:27:10 rb_tinytds_raise_error(0x01e8f580, 0, DBPROCESS is dead or not enabled, error, 1, 20047, 0)
05/19/11t08:27:10 rb_tinytds_raise_error(0x01e8f580, 0, DBPROCESS is dead or not enabled, error, 1, 20047, 0)

I don't really know how your library works, I'm just guessing. Do you have an idea?

[metaskills]

Morning @madprog,

Connection timeouts are very difficult to handle and debug. I have done a lot of work in TinyTDS to try and cope with these at the DB-LIB level as much as possible. More times than not, FreeTDS will leave the dbprocess dead. This is why we have an #active? method on the client now and higher level adapters like Sequel and the ActiveRecord Adapter to hook into. Check out the readme for details.

Now that said, let me see if I can help you trace where the issue may be on your end with some helpful tips. First tho, I want you to know that I have tested TinyTDS extensively (locally) and even on Azure. I mention SQL Azure since that access is TCP/IP based and potential to network issues that may come up in when going far out over the internet. To date I have never had an issue with latent/bad connections. I have seen reports from some users on other platforms have issues with the nextwork. I have never been able to track down if it is their platform or network layout.

What platform are you on? What version of FreeTDS? What is your network topology to said DB server, local, far off? What is your :timeout setting? What other library are you using with TinyTDS, Sequel or AR Adapter or none?

[madprog]

Hello @metaskills,

The problem was not located in libraries. I was accessing the database via a VPN connection, and for a reason it dropped the connection after some amount of data. Using a SSH tunnel is just fine!

Thank you for your response, it put me on the way :)

[metaskills]

This is an article on SQL Server about keep alive. It states the following:

http://blogs.msdn.com/b/sql_protocols/archive/2006/03/09/546852.aspx

It causes good connection to break during transient network failures. 
So configuring the keep-alive values too small is not recommended

I can conclude the most network errors are related to low server keep alives under poor conditions.