Frequent vague timeout errors (TinyTds::Error)

[rails-sqlserver]

Frequent vague timeout errors (TinyTds::Error)

[metaskills]

Not much to go on here.

1. What is your current timeout setting?
2. Have you identify what SQL is timing out? Would be seen in the logs before said error you posted?
3. Do you feel that that the timeout is working incorrectly? IE, can you set it to say 0 and get the same problems?
4. Is your DB local or remote, can a possible network issue cause the timeouts?
5. etc, etc...

Many questions. Can you do a roundup and post back?

[DougPuchalski]

We certainly may have performance issues, as this is a legacy DB that needs optimization. That will be done, but I am hoping there's a way to recover from this, i.e. disconnect and reconnect without having to restart the ruby process.

1. Previously 4000, now trying with 4. Same results. I'm still a little unsure if this is ms or s. Can you clarify?

2. SQL is nothing intensive. Looking at the logs I see occasionally TinyTds::Error: Unknown marker and TinyTds::Error (Read from SQL server failed.) before I see the other error, but then the latter happens continuously.

3. I have not tried this. If set to zero, what behavior would you expect? More, or less errors?

4. Remote, but I have seen same issue when running locally

Thanks for your help and quickly responding to the issue!

[metaskills]

In general, a timeout error for a sql batch is something TinyTDS can handle just nicely and ActiveRecord builds on top of that and works just fine too. Some things you have to look out for are timeout errors on getting something across or back on the connection. Those are real tricky, we had updates in TinyTDS 0.4.3 IIRC and with the latest adapters to cope with lost/dead/timeout "connections" by using TinyTds::Client#active? method. So you should tell me too what versions you are using and I may suggest an upgrade. Other points.

If you specify a timeout in database.yml, the adapter will convert it to seconds. AR convention is 5000 == 5 seconds. So setting it to 4 would be really low, 0 I think. Details here:

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/lib/active_record/connection_adapters/sqlserver_adapter.rb#L374
https://github.com/rails-sqlserver/tiny_tds/blob/master/lib/tiny_tds/client.rb#L57

So basically, a 0 may just get passed on down to mean unlimited timeouts. Which is something you may want.

[DougPuchalski]

I've been on TinyTDS 0.4.4. So sounds like I already would have those enhancements.

Any debug suggestions? I couldn't say whether it's your gem, freetds, the db server, network, or what.

Restarting the app server wakes things up, I'm wondering if there's another way to reset the connection.

[kalabiyau]

Having the same - first, for example, 5 request, are ok, then - i have same error 'TinyTds::Error: Attempt to initiate a new Adaptive Server operation with results pending: dbcc useroptions'

And another idea - in sqlserver profiler i see than batch request is completed. But tinytds - thinks, that server did not send anything :(

I am using
tinytds 0.4.5
MSSQL server 2008

[kalabiyau]

but if i use some long loop with the same query

100.times do Task.all.each do |task| puts task.description; task.evalues.each do |val| puts val.extparamvalue end end end

All is done like it sholud - no 'pending' message appears.

[kalabiyau]

And some interesting thing first i get argument out of range and then results pending.
But, a have set coerce_sqlserver_time :datetimevalue and cannot now understand what is a problem

timeouts a set to 15000

[kalabiyau]

ActiveRecord::Base.default_timezone = :local

resolves the problem for me

[metaskills]

This page on FreeTDS talks about the "Unknown Marker" errors. http://www.freetds.org/faq.html#unknownmarker

It just so happens that I updated the project's README covering the topic about the TDS version. See the section "FreeTDS Compatibility & Configuration".

[metaskills]

@kalabiyau Running your AR default timezone to :local is not a good solution, especially if your world wide web application shows times to users from the database and those times in the DB are UTC, which they should be. See the rails guides on this topic.

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

[linuxonrails]

ActiveRecord::Base.default_timezone = :local

It doesn't work to me :-(

(ActiveRecord::StatementInvalid: TinyTds::Error: Attempt to initiate a new Adaptive Server operation with results pending)

[metaskills]

The "Adaptive Server operation with results pending" message usually indicates an improper usage of TinyTDS and not closing the result set by iterating over it.