Dear all,

We have a set of scheduled DTS jobs which run OK for months on SQL2000 SP3.
Recently (since mid July) we found them hang up and cannot finish. Symptoms
as below :-

1) The hang up only happens on those DTS jobs with ActiveX script steps,
while DTS jobs executing plain T-SQL / data pump is not affected
2) The DTS package while run on console also hang up at the active X Script
step
3) The problem gone after system reboot, however, this does not last long.
On one occasion, after a reboot, I can run such a DTS job several times,
then, the hang up happens again. On another occasion, after a reboot last
night, I can run the DTS job once, then the hang up happen again.
4) When the hang up happens, I can not start Control Panel, however, File
Explorer is OK. (I cannot figure out what is in common among DTS panel vs
Control Panel)
5) I have try to use Task Manager / Process Viewer to look at what happens
when it runs, I just notice that DTSRUN.EXE stuck there. In normal time, the
DTSRUN.EXE just shown up and gone after finish
6) The job can be cancelled, DTSRUN also gone after that.

Research done so far :-
1) We are suspecting there should be some sort of waiting occurs during
running the ActiveX steps, but don't know what it wait for.
2) From MS support web site, we notice 2 article with relevance
3) KB290077 - DTS scheduled job does not complete after first run, however,
as we are running SQL2000 SP3, we do not think it applies.
4) KB318819 - A DTS Package raises exceptions or stops responding when you
run it as a scheduled job. The work around as described has already applied
(e.g. execute on main package thread), but nothing change occurs.

Even if no in place solution exist, I would be grateful if anyone can
suggest proper steps to follow to continue on the investigation.

Thanks ... Eric

Eric,

What is it that ActiveX tasks are doing?

Ilya

"Eric Fung" <EricFung (AT) discussions (DOT) microsoft.com> wrote

Dear Ilya,

Thanks a lot for your kind attention.

The ActiveX tasks of those jobs in problem handle a variety of different
tasks (e.g. initialize global variable as parameter for data extraction,
collect size information of directory, etc).

As it seems the hang up is irrelevant to what the ActiveX tasks do, later on
yesterday I started to test with simple VBscript from command prompt with
wscript. The script also hangs up, even on single statement 'wscript.echo
"xxx"'.

So now I think it may not related with DTS, instead, probably related with
the scripting engine / Windows itself.

BTW. As these jobs used to run OK for months, have you heard of any recent
patches from MS which can cause similar problems ?

Thanks .... Eric


"Ilya Margolin" wrote:

Have you tried to run any DBCC processes on your msdb database?

Rick

"Eric Fung" <EricFung (AT) discussions (DOT) microsoft.com> wrote

Thanks Rick.

I do carry out DBCC on msdb frequently (e.g. the default maintenance plan
will do the DBCC CHECKDB), However, I cannot figure out the relationship
among the job hang up and the DBCC action.

In one occasion, I reboot the machine, and then run an activeX script to
collect directory size info. Well before any other job runs (so no DBCC runs
in between), the job completed successfully only once and then failed again
on any further attempt.

Rgds,
Eric



"Rick Gittins" wrote:

Eric,

Do you have free space on the server's drives?

Ilya

"Eric Fung" <EricFung (AT) discussions (DOT) microsoft.com> wrote

Thanks Ilya.

Yes, the disks do have free space.

At the moment, we found there may be some glues relating my case - about
vbscript code hang up, with system monitor setup on remote machine for
performance monitoring on those affected machine. However, due to all the
machines affected can not be freely reboot, my progress is very limited.

Thanks ... Eric

"Ilya Margolin" wrote:

can check the the log whether the DTS package is executed or not... i had
issue like.. DTSRun doesn't exit after successful DTS package execution.. we
were able to run this from enterprise manager.. are you able to do this...
try installing SP4 or the client m/c in which you are tying execute the
DTS... it worked for me.

-Spsk

"Eric Fung" wrote: