Execute SQL Task Hangs
-
I have a DTS package with an Execute SQL Task that executes a stored procedure which updates two columns in a staging table. This task has performed without problems in the past but has started hanging. When I execute the stored procedure in QA, it completes in about 5 minutes. When it is executed via the task object in the package it runs for over an hour without completing. I end up having to kill the process via QA. There are no errors in the error log and the rest of the tasks in the package complete without problem. The environment is SQL Server 2000 running on a four processor box with windows server 2003, all with the latest service packs and update patches.
I am somewhat at a loss as to what or where to begin checking in order to resolve this issue so would appreciate any ideas anyone may have.
Thank you,
Michael
Michael Weiss -
1) Do you have Package logging enabled? If so, is there any messages there?
2) Can you modify the stored procedure to provide some progress messages. Either write to a table or use xp_logevent.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
Hi Michael,
We are experiencing very similar problems (same sort of set-up, 2 & 4 way boxes, Win2k and 2k3). To date, we've come up with nothing. All of our DTS packages, being run from CMD line or SQL Agent (DTSRun.exe in both cases) have started failing intermittantly.
Some of the fails are identical to issues supposedly fixed in (SQL) SP1, we're running SP3a, so definitely shouldn't be happening.
I'm starting to worry that it's a MSSQL issue rather than environmental. If you come across anything, please post it here as we'd be very interested to find out what you learn.
Steve.
Steve.
-
What does the query plan for the SQL statments being executed look like? Is there a lot of parallel execution taking place? What sort of processors do you have? Maybe it's a threading issue
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
Because it works in QA, it must be DTS related. Is the package scheduled to run as a job? Did you check the process? Any blocking? Is the transaction log space used growing as the package executes? Is the transaction level different in QA versus DTS? Are the other tasks running at the same time in the package? Does it matter where the package is executed? Perhaps you are running out of resources. Did you try setting the max tasks to 1? Will it run in a new package with the T-SQL as the only step? Can you alter the T-SQL with as SET ROWCOUNT to limit the query to see if it will finish if it affects fewer records. Are any tasks logging to a file across a slow network? CPU usage? IO? Any email? (I've seen a failed email hang a job. The scheduler also hung until the job was killed via sql server agent restart. The exchange profile, like pathnames using letter drives, depends on where the package is run.) Is the priority level "low"? Perhaps a trace might reveal something.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
Thanks Steve, Phill, and rstone for the suggestions and input. I fixed the issue by removing the /PAE and /3GB switches from the boot.ini file and disabling AWE in sql server. Now the query completes just fine as it did before I tried to get sql server to use more of the available RAM. Only problem is, now I have a win2k3 box with 8gb of RAM, most of which is sitting there unused. I searched on the Windows Server MS newsgroup and posted a question there on how to properly configure win2k3 Enterprise edition and sql server to utilize PAE/AWE and have not yet received a reply. Other than that, I have no idea what to try next and will probably let the issue rest for a while until I get more time to play with it. If I do find out anything though, I will be sure to post it here so all can see...
Thanks,
Michael
Michael Weiss
Michael Weiss -
-
Steve et al,
I was able to make the query work quite nicely by setting the Option MAXDOP(1) in the query. Tom Moreau's suggestions at http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.server&mid=2f59d0ed-086f-4bd2-8117-b05882d7f1d4
(watch for the wrap there, that is the sql server community newsgroup) and Phill's suggestion of checking the parallelism pointed me right to the problem.
Steve, regarding DTS packages in particular not functioning correctly, I simply copied my DTS packages from my win2k server over to the win2k3 box, opened them in DTS, changed the server connections, saved them to the server and everyone has functioned perfectly since then...including packages being run by scheduled jobs so I can't offer any suggestions to help with your difficulties. I will continue to keep my eyes open for info on that issue though and let you know if I find anything. Thanks again for everyone's input.
Michael
Michael Weiss
Michael Weiss -
I should have added that the cause of the intermittent nature of the problem must have been related to variances in data and perhaps quantities. In either event, disabling parallelism solved the problem.
Michael
Michael Weiss
Michael Weiss
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply