Issue with TDP Hanging up in Automation

Toad Data Point
1

Hi, I’m experiencing an issue with our TDP 3.3 product in which our automation is essentially hanging up mid process and not finishing up. The problem is, is during the process, the query is either completing or cancelling, because the query is no longer being pulled against the server that its hitting when we investigate. Therefore, I’m not sure why TDP is not getting some error or why it doesnt continue. I’m attaching a copy of the automation file when it looks fine. Since we can only add one attachment, I’m going to paste the spot that it’s hanging on when it has issues below. It should be about a ten-twenty minute process, but will hang up on this process in the automation and stay there until I come in the next morning (about 10 hours) until I have to kill it from the task manager and restart it. It’s an ODBC connection query that always fails at this point in the process. Usually when I restart the process it works just fine after that. It’s not every time, but just does it about once or twice a week. It’s a daily task and runs through the task scheduler calling the batch mode of TDP.

My question is, is how do I get it to either time out and send and error or discontinue running the task? The only way at this point is by seeing that there’s no “completed” email sent at the end, in which we know there’s a problem. I’d like to be able to say if the process doesn’t finish by a certain time, loop and try again, or error completely. I’m just trying to get something better than just staying hung up until the next morning. Any ideas on this issue would be very much apprciated, because we’re still in a testing phase of this software on our server and are reluctant to bring in more processes if just a few are already causing hangups and data not to finish.

Thanks!

Issue File:

– 12/24/2013 8:32:10 PM: VIP_CIDTV_ETX.log: Setting up environment
– 12/24/2013 8:32:10 PM: VIP_CIDTV_ETX.log: Script run by Toad Data Point 3.3.0.1526
– 12/24/2013 8:32:10 PM: VIP_CIDTV_ETX.log: Build started
– 12/24/2013 8:32:11 PM: VIP_CIDTV_ETX.log: Compiling Script
– 12/24/2013 8:32:34 PM: VIP_CIDTV_ETX.log: Build Completed
– 12/24/2013 8:32:35 PM: VIP_CIDTV_ETX.log: Begin execution script activities
– 12/24/2013 8:32:35 PM: VIP_CIDTV_ETX.log: Running export template \suddenlink.cequel3.com\data\COR\CrossDepartment\ITBillingTeam\Reporting\Toad\VIP_CIDTV\Templates\ADD_VIP_ETX_Template.txp
– 12/24/2013 8:32:35 PM: VIP_CIDTV_ETX.log: Excuting export using connection IcomsHA (SDLODBCCB)
– 12/24/2013 8:34:15 PM: VIP_CIDTV_ETX.log: Attempted to export 74 rows.
– 12/24/2013 8:34:15 PM: VIP_CIDTV_ETX.log: Successfully exported 74 rows.
– 12/24/2013 8:34:16 PM: VIP_CIDTV_ETX.log: Export Finished, click VIP_CIDTV_ETX.accdb to view file.
– 12/24/2013 8:34:16 PM: VIP_CIDTV_ETX.log: Running export template \suddenlink.cequel3.com\data\COR\CrossDepartment\ITBillingTeam\Reporting\Toad\VIP_CIDTV\Templates\REMOVE_VIP_ETX_Template.txp
– 12/24/2013 8:34:16 PM: VIP_CIDTV_ETX.log: Excuting export using connection IcomsHA (SDLODBCCB)
– 12/24/2013 8:34:59 PM: VIP_CIDTV_ETX.log: Attempted to export 25 rows.
– 12/24/2013 8:34:59 PM: VIP_CIDTV_ETX.log: Successfully exported 25 rows.
– 12/24/2013 8:34:59 PM: VIP_CIDTV_ETX.log: Export Finished, click VIP_CIDTV_ETX.accdb to view file.
– 12/24/2013 8:35:00 PM: VIP_CIDTV_ETX.log: Running export template \suddenlink.cequel3.com\data\COR\CrossDepartment\ITBillingTeam\Reporting\Toad\VIP_CIDTV\Templates\ADD_CIDTV_ETX_Template.txp
– 12/24/2013 8:35:00 PM: VIP_CIDTV_ETX.log: Excuting export using connection IcomsHA (SDLODBCCB)
– 12/24/2013 8:36:08 PM: VIP_CIDTV_ETX.log: Attempted to export 45 rows.
– 12/24/2013 8:36:08 PM: VIP_CIDTV_ETX.log: Successfully exported 45 rows.
– 12/24/2013 8:36:09 PM: VIP_CIDTV_ETX.log: Export Finished, click VIP_CIDTV_ETX.accdb to view file.
– 12/24/2013 8:36:09 PM: VIP_CIDTV_ETX.log: Running export template \suddenlink.cequel3.com\data\COR\CrossDepartment\ITBillingTeam\Reporting\Toad\VIP_CIDTV\Templates\ADD_CIDTV_EQP_ETX_Template.txp
– 12/24/2013 8:36:09 PM: VIP_CIDTV_ETX.log: Excuting export using connection VIP_CIDTV_ETX
– 12/24/2013 8:36:24 PM: VIP_CIDTV_ETX.log: Attempted to export 45 rows.
– 12/24/2013 8:36:24 PM: VIP_CIDTV_ETX.log: Successfully exported 45 rows.
– 12/24/2013 8:36:25 PM: VIP_CIDTV_ETX.log: Export Finished, click VIP_CIDTV_ETX.accdb to view file.
– 12/24/2013 8:36:25 PM: VIP_CIDTV_ETX.log: Running export template \suddenlink.cequel3.com\data\COR\CrossDepartment\ITBillingTeam\Reporting\Toad\VIP_CIDTV\Templates\REMOVE_CIDTV_ETX_Template.txp
– 12/24/2013 8:36:25 PM: VIP_CIDTV_ETX.log: Excuting export using connection IcomsHA (SDLODBCCB)
VIP_CIDTV_ETX_GOOD.log (9.53 KB)

2

Hi, I know the holidays might have came and this post may have been pushed back, but if anyone has any ideas, I’d greatly appreciate it. I’m also watching the best I can just running it in TDP, as opposed to the task to see if I can notice anything else. I do notice a Read Buffer Size set to 5000, not sure exactly what this means, but I will say that the data being pulled, although not resulting in a lot of records is a pretty complex query that looks into a large set of data, (multiple tables over a million records), so maybe the size and length of time the query is running is causing issues? I have check system resources unchecked. Should I check this and maybe see what comes back from this as well? Thanks again

3

Let;'s check/change a few things:

  1. Change your buffer size to 500 or less. We process rows in chunks. 5000 maybe to large.

  2. Go to tools|Options and look at the Database|Timeouts tab. Change the execution time out to 1000 and change the connection timeout to 60.

  3. Turn on Verbose logging in your automation script.

  4. I would also try adding an export activity above the one that is having the issue. Change the export type to something other than access. Try exporting to CSV.

Hopefully making these changes will jog the process and let us know what is occurring.

4

Debbie, I did all of those things and performance increased quite a bit, as well as the query information finishing. I’m going to keep verbose on and keep an eye on this going forward, but I think one of the tips has this resolved and I really appreciate your help!

Chad

5

That is great news.