On Data Servcies XI (v 12.2.3.1) we have a batch job that uses CDC. Most of the time the job would run fine, but usually during times of higher volumes the batch job would fail with an "SQL submitted to ODBC data source <database> resulted in error <[Microsoft][ODBC SQL Server Driver]Timeout expired>", followed with the failed sql command. We would restart the job and, looking at the logs via the DS Designer, it would grab some rows from the Distribution database for processing but then appear to "get stuck" after only a few seconds and sit there until the connection timed out (set to 15 minutes) and no records made it to the target table. We could take the failed SQL and run it manually and it would complete in a second or two. After trying many things including increasing the timeout, changing the dataflow, running sql profiler, droping indexes....nothing helped. To limp along we could do nightly refreshes of the data and eventually the problem would go back into hiding after the high volume went away. The batch job is for a GL datamart so the higher volumes would come at month\quarter\year end processing when the business was closing the books. Oddly enough there are 6 other marts on the same instance and none of them were experiencing the same issues.
Our DBA found an error in the SQL logs "A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 64, output error: 0).", however the error ususally didn't match up with the times the job failed. After researching the error we found the Chimney Offload setting was turned on on the server. Microsoft KB 951037 (http://support.microsoft.com/kb/951037) outlines how to check to see if its running and how to turn it off. Once this was turned off the job started running normally. Our DB server is a Windows 2008 R2 cluster running a SQL 2008 instance.
This problem caused us so many problems and took so much time to resolve that I'd figure I'd post it out there to hopefully help someone else.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.