I have a DTS package that for the past 4 weeks has taken between 3.5 and 4.5
hours to run each night. Without any other things going on or additions to
the DTS, the job jumped to 6.5 hours two nights in a row, and then
skyrocketed to over 22 hours.

I removed the 15 queries, out of over 65 queries, that take the most time
and put them into a separate DTS to experiment. This didn't help.

The data warehouse hardware is new and there are not very many users or
applications hitting it. There is nothing else running on the hardware. We've
rebooted the server, but that didn't help.

I'm guessing the problem has to do with database logging. The log file for
this database is 2.74 GB and the database itself is 2.67 GB. There is over
300 GB free on the server. The log files and the database files are on the
same drive, which I realize isn't optimal, but this has always been the case.
We plan on seperating them, but I doubt this is the cause of the sudden jump
in process time.

The DTS package actually drops all of the records from all of the tables
each time it runs, then reloads them from the source and performs the
transformation updates. With this in mind, logging isn't all that important
from our perspective for this database.

Any ideas would be greatly appreciated...

Thanks,
Rob

OK So some ideas


DROP indexes on the destination and reapply afterwards
Make sure the log and data file size are not going to need to expand during the import
Set the Recovery mode to SIMPLE
Make sure there are no triggers on the destination.
Any users on the system at the same time?



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Robert Hamilton" <RobertHamilton (AT) discussions (DOT) microsoft.com> wrote

On Thu, 5 May 2005 09:51:03 -0700, Robert Hamilton
<RobertHamilton (AT) discussions (DOT) microsoft.com> wrote:
Do you mean it drops the tables, or truncates the tables, or deletes
the data? Are you sure you're dropping *all* the data? Because
something that degrades like that sure sounds like a table getting
larger every day. And you changed NO, and I mean NO, code, between
the early runs and the later runs?

Can't you see which steps are taking longer?

Sounds like something blatently obvious that you're overlooking.

Josh