activerecord-sqlserver-adapter: TinyTds::Error: DBPROCESS is dead or not enabled

Hi @metaskills. I’ve been running now for a month with ActiveRecord::Base.clear_active_connections! and have had no issues since. Thanks for your help!

OK, I did a big round up on this. Tons of info below!

Test TinyTDS Idle For 24 Hours

No issue whatsoever. Connection worked great!

Does SQL Server Reap Idle Connections?

Not that I could find. I know Azure does this and that is why /tiny_tds/162 is open. Here are some articles I read on how others might choose to do this in SQL Server. Something to check for.

• Expiring sessions after 15 minutes on SQL Server
• Configure a timeout value for an inactive database connection
• NOTE: Check to make sure you do not have HAProxy or some other proxy killing connections.

Does ActiveRecord Reap Dead Connections?

Not anymore! Tho it still has this reaper_frequency for database.yml which is pretty much deprecated. In the Rails core issue link further below, it talks about this old setting ab it. This article below and embedded links warn against it too. Knowing this, I am now going to ask people if they have this accidentally left in and recommend they remove it. Have you?

• The Rails Grim Reaper

How Does ActiveRecord PostgreSQL Adapter Handle This?

First, I did a QA of ActiveRecord’s connection pool’s checkout and verify code. The connection pool only verifies a newly checkout connection which only happens when it assigns one to the current thread. So assuming a simple IRB session like so, this happens in my personal Rails 4.2 application using PostgreSQL.

> ActiveRecord::Base.connection.execute 'SELECT 1'
=> #<PG::Result ... >

# Kill Or Restart PostgreSQL

> ActiveRecord::Base.connection.active?
=> true
> ActiveRecord::Base.connection.execute 'SELECT 1'
PG::UnableToSend: : SELECT 1

It is worth pointing out that if I restart the database between reloading web pages that it recovers just fine! The reason being is that ActiveRecord has a ConnectionManagement middleware which calls clear_active_connections! which releases & checks in each connection. This sets up the next request to check out a connection which is also an implicit verify as well. So to be clear, there are two different behaviors here and I think we both assumed that connections in things like workers are checked out (and verified) every time for the current thread. This is not the case! It is only true for requests/responses.

I suggest reading the following Rails core issues in full. The first is long and the solutions are the following two merged issues.

• Rails 4.0.0 and 4.0.1 PostgreSQLAdapter does not properly check whether a connection is active
• Reaper has access to threadsafe active? call
• Track owning thread for connection pool

Why Not Make Our Own Reconnect Strategy

I dropped this for a reason. It is hard to do! Reading thru those core issues proves my point on that. I want to solve this in way that allows Rails to do the heavy lifting.

So Where Does This Leave Us?

I am not sure. It seems to me we are doing exactly like PG does. We use raw_connection_do 'SELECT 1' in our active check and follow all the best practices. That said, here is one thought:

1. Use ActiveRecord::Base.clear_active_connections! - Perhaps if you have a periodic job that uses the same thread / connection, you should clear active connections before/after your work to simulate what Rails does around each controller request for a long lived app.

Wow, awesomely comprehensive look at this issue.

Given that this is a fairly new application, and I’ve never set any reaper configuration, I was fairly sure this wasn’t the issue. Just to be sure I searched my codebase for reaper_frequency and reaper, both turning up no results.

I had also noticed that AR only seems to verify connections when they are assigned to theads, interesting to hear about the middlewear to release them between requests.

I agree that it seems that ActiveRecord::Base.clear_active_connections! looks to be a promising solution in job queue workers. I’ll try it out on my worker, and then see if I can suggest an update to the worker gem.

I’ll keep you posted @metaskills.

@metaskills The issue appears to occur after a long period of inactivity, rather than during a long-running task. Once the error has occurred the application doesn’t seem to attempt to reconnect, I have to manually restart.

I’ve talked to the DBA in charge of the SQL Server in question, who says that it is not configured to automatically close connections, and that backup procedures shouldn’t be interrupting connections.

I’ve been having a closer look into the particulars of the situation, and I note that this only appears to occur in our job queue workers, which uses Que. I’m wondering if this could be implicated and will need to look closer.