[Python] Pymssql: "DBPROCESS is dead or not enabled" error every time I run my sixth query, no...

The Problem


I have an AWS Glue job that uses the standard set of analytics libraries with Python Shell. Of special note, it uses SQLAlchemy to connect to our Microsoft SQL Server. We also go out of our way to import pymssql 2.3.1 for this. In this Glue job, we run multiple copies of the same stored procedure six times in sequence. Seemingly at random, the sixth (and therefore final) stored procedure will run to completion from SQL Server's point of view but fail inside its pandas.read_sql call. The error message, which as best as I can tell comes from FreeTDS, is

Operational Error("pymssql.exceptions.OperationalError) (20047, b'DB-lib error message 20047, severity 9: DBPROCESS is dead or not enabled ')
​

Its type is sqlalchemy.exc.OperationalError.

The data on our SQL box only changes once per day, but here's the weird thing: When I get this error, I get it for the rest of the day. It does not matter how many times I rerun the Glue job.

How can I debug this?

What I've Tried or Ruled Out


Sorry for the length, but I've tried a lot:

• I am certain that the SQL box has no issues. The final query runs to completion from its point of view. Neither Extended Events, sp_WhoIsActive, nor the SQL Server Error Log show anything of interest. They know when Python closes the connection, but that's normal.
• Changing pymssql version as suggested here did nothing. I've tried everything below with both 2.3.0 and 2.3.1.
• There are no AWS permissions issues. This works just fine most days of the year.
• Adding stream_results to my connection options has no effect, whether True or False. The documentation isn't clear on if Microsoft SQL Server even supports it.
• I've added echo="debug", echo_pool="debug" to my engine options. The additional logging that they add has been of no value. I won't even bother to share it here. It ticks along perfectly until we get the same error as I've put above.
• pool_pre_ping = True is by far the most suggested solution. It does not solve the problem. I can see the pre ping in the additional logs discussed above, but the job still fails in the same way.
• Disabling connection pooling (poolclass=NullPool) did not fix the problem, nor did pool_reset_on_return.
• Given the above, I feel that pooling issue are ruled out. But I do find it interesting that the default number of pools is 5 and I'm failing on query 6.
• I have hunted very deeply on Google for this error. I have read every single page of Google results for "20047 db-lib error message dbprocess is dead". This includes items beyond Python (Ruby seems to share this issue). Nothing has worked.
• It's not a problem of having too much data. We have another Glue job that collects a superset of this data and it worked just fine today. In all aspects expect for a change in stored procedure name, the other job's code is totally identical.
• I see nothing to indicate that this is a timeout issue.
• Spark is not an option. It just isn't. I'd be open to trying pyobdc, but I've never got it to work with the Windows EC2 that we're hitting.

Adding even more calls to the stored procedure fixes the problem (e.g. I can cover all of 2024 either in one run or by doing one half of 2024 and then the other half), but I don't want to do this due to performance costs. It also feels like cheating rather than finding the root cause.

Continue reading...