TinyTds::Error: DBPROCESS is dead or not enabled

[thomasfedb]

I'm receiving this error from a long-running worker process that periodically accesses a SQL Server database. Curious that some ActiveRecord machinery isn't attempting a reconnection, if that's the issue.

Full trace:

vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:308:in `execute': TinyTds::Error: DBPROCESS is dead or not enabled: EXEC sp_executesql N'SELECT [uvStudyLabTutors_CCGS].* FROM [uvStudyLabTutors_CCGS]' (ActiveRecord::StatementInvalid)
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:308:in `raw_connection_run'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:299:in `_raw_select'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:295:in `block in raw_select'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/abstract_adapter.rb:466:in `block in log'
    from vendor/bundle/ruby/2.2.0/gems/activesupport-4.2.0/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/abstract_adapter.rb:460:in `log'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:295:in `raw_select'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:244:in `sp_executesql'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:19:in `exec_query'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:199:in `select'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in `select_all'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/querying.rb:39:in `find_by_sql'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/relation.rb:638:in `exec_queries'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/relation.rb:514:in `load'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/relation.rb:243:in `to_a'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/relation/delegation.rb:46:in `each'
    ...
`

[metaskills]

Hey @thomasfedb, that is interesting. The adapter should be doing some rudimentary connection management, see here. Have you tried Rails 4.2.1 or investigated this further?

[krzcho]

same here with rails 4.1.10 and 4.1.0 adapter
i have reconnect: true option in db.yml but i am not sure if it is being used

[krzcho]

it looks like it is not recommended to use this option (http://stackoverflow.com/questions/21594429/database-yml-in-rails-reconnect-set-to-true-or-false) in case of a connection being lost during a transaction
mine sql has a standard connection timeout of 10 mins so it probably would be simplest to do an each 5 mins dummy query to keep it opened

[metaskills]

There is no more reconnect option in 4.2 and upward. We simply conform to ActiveRecord's connection pool management protocol and it should do the rest. Also, I just did a pre-release of TinyTDS 0.6.3.rc1 too. Maybe try that as well. Note, this pre-release is POSIX only. No windows binaries.

[thomasfedb]

Hey @metaskills.

I last saw this error when using Rails 4.2.0. I've now upgraded to 4.2.1, and I'm waiting to see if the same error occurs. Is there anything in particular in 4.2.1 that should fix this error?

If I see the error again, I will try with the TinyTDS pre-release.

[metaskills]

Nothing specific. I did some general 4.2.1 cleanup around connection management while I was working on transaction support changes.

[thomasfedb]

Hey @metaskills. I have updated to 4.2.1, and so far no further issues. I'm going to give it a few more days, as it was previously erroring out only after a few days.

[metaskills]

Great, thanks for the update. Will keep this open. When you report back, let me know if you updated to the 0.6.3.rc1 version of TinyTDS too.

[thomasfedb]

I haven't upgraded TinyTDS to 0.6.3.rc1 yet, as my issues don't seem to stemming from that, though I'll try it if Rails 4.2.1 doesn't fix the issue.

[thomasfedb]

The issues has recurred, here's the trace with Rails 4.2.1.

vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:308:in `execute': TinyTds::Error: DBPROCESS is dead or not enabled: EXEC sp_executesql N'SELECT [uvStudyLabTutors_CCGS].* FROM [uvStudyLabTutors_CCGS]' (ActiveRecord::StatementInvalid)
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:308:in `raw_connection_run'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:299:in `_raw_select'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:295:in `block in raw_select'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:473:in `block in log'
    from vendor/bundle/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:467:in `log'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:295:in `raw_select'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:244:in `sp_executesql'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:19:in `exec_query'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:199:in `select'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in `select_all'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/querying.rb:39:in `find_by_sql'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/relation.rb:638:in `exec_queries'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/relation.rb:514:in `load'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/relation.rb:243:in `to_a'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/relation/delegation.rb:46:in `each'
    ...

I'll try upgrading TidyTDS to 0.6.3.rc1.

[thomasfedb]

As it happens, silly me for not checking earlier, I'm already on master for TinyTDS:

gem "tiny_tds", git: "https://github.com/rails-sqlserver/tiny_tds.git"

Given this, what's the next avenue of enquiry?

[metaskills]

As it happens, silly me for not checking earlier, I'm already on master for TinyTDS

Well, just because you are bundled to master does not mean you are on the 0.6.3 version. Do you know what SHA is in your Gemfile.lock?

Given this, what's the next avenue of enquiry?

I just always like to check. Nothing in the change log strikes me as affecting you.

[thomasfedb]

I'm on 0.6.3, specifically ed4e43ec2598fc7879359f53b6d941b9e92234bc.

Looking at the comparison ed4e43e...master I don't see anything that should affect this.

[thomasfedb]

Hey @metaskills, this continues to be an issue.

Here's the latest trace from an occurrence earlier this evening:

vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:308:in `execute': TinyTds::Error: DBPROCESS is dead or not enabled: EXEC sp_executesql N'SELECT [uvStudyLabHouses_CCGS].* FROM [uvStudyLabHouses_CCGS]' (ActiveRecord::StatementInvalid)
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:308:in `raw_connection_run'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:299:in `_raw_select'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:295:in `block in raw_select'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:473:in `block in log'
    from vendor/bundle/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:467:in `log'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:295:in `raw_select'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:244:in `sp_executesql'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:19:in `exec_query'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/database_statements.rb:199:in `select'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in `select_all'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/querying.rb:39:in `find_by_sql'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/relation.rb:638:in `exec_queries'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/relation.rb:514:in `load'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/relation.rb:243:in `to_a'
    from vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/relation/delegation.rb:46:in `each'

Relevant bundler output:

  * activerecord-sqlserver-adapter (4.2.4)
  * tiny_tds (0.6.3 ed4e43e)

[metaskills]

Hmmm... do you know if the error happens during the long task or directly after the period of time it sat idle? If the later, I wonder if there is a server timeout that is happening? Similar to this issue? rails-sqlserver/tiny_tds#162