DTS ODBC Connection Hang on Query Completion
-
This is a problem so bizzare to me that I am 100% stumped... not even a scrap of idea what to do. Here's the issue:
During our ETL steps for loading our data warehouse (Red Brick Warehouse), we perform a process by which we pull records to be processed on a particular load from Red Brick into SQL Server. A temporary table is built with the primary keys for the records to be changed, and that table is joined with the table containing the data in a select statement within a DTS transform data task. Seemingly randomly, but with enough regularity to cause concern, our load process will hang at one of these steps. The load is launched by a SQL Agent job.
Investigation during the hang shows no locks on either the Red Brick Warehouse or SQL Server. Further investigation on Red Brick shows the query in a state of "returned", seemingly waiting for a response from SQL Server on the other side of the ODBC connection to tell Red Brick, "ya, I got your message" so that Red Brick can truly complete the job.
When the load process is stopped by manually stopping the SQL Server Agent job, the hung query completes! I don't even have to kill any jobs.
To continue the load process all we need to do is restart the job. Any ideas on what the heck could be causing this intermittent but nasty problem?
Thanks
-
I may have found the problem and it seems to have nothing to do with SQL Server. I confirmed the ODBC driver for Red Brick is an old version, predating the following release note:
v CSL #344684
When multiple ODBC threads were executing concurrently and many different
error messages were generated at the same time, the Red Brick ODBC Driver
would periodically hang with a very high CPU utilization
I will update the driver and see if the problem disappears.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply