Re: DTS Performance

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:73A473C0-7334-4BC1-AA4B-AE884E0237B8@xxxxxxxxxxxxxxxx
>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
>


.

Relevant Pages

  • Re: SQL 2000 - Connection Error with DTS Packages
    ... After I changed all SQL Server 'Database Connection' steps in each of my DTS ... Package" option when you right click a DTS package name in ME. ...
    (microsoft.public.sqlserver.dts)
  • Re: Access database stability problems
    ... The backend data files are even located on the same network drive on the server. ... I found out today that people running that database are NOT having crashes like we are. ... What I wanted to strongly recommend is moving your database to an SQL server of some kind, SQL Server 2005 Express if nothing else. ... You haven't said whether 'crashing the database' is just a matter of Access losing its network connection and screaming blue murder, or whether it's actually breaking the backend data file. ...
    (microsoft.public.windows.server.sbs)
  • Re: file conversion from SQL Server to Access
    ... You want to take data and table structure from SQL Server to Access? ... You can also have DTS create the destination tables for you. ... Is there a Northwind database ... >> www.konesans.com - Consultancy from the people who know ...
    (microsoft.public.sqlserver.dts)
  • Re: Audit of sql server
    ... The DTS database transfer makes sense now. ... >> SQL Server will not check on this for you. ... >> Compiles each query but does not execute it. ...
    (microsoft.public.sqlserver.programming)
  • Re: cannot open database requested in login
    ... Is the connection object using Windows Authentication? ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ... > I have a DTS replication which truncates the destination> of one database and copies all the rows from the source> table of another database of same serverin> first step. ...
    (microsoft.public.sqlserver.dts)