Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2007
    Posts
    4

    Unanswered: SQL Server 2000 SP4 slows down dts job

    Hi peeps,

    We have just upgraded to Service Pack 4 on our SQL Server 2000.
    We have had a DTS job that normally takes about four hours to complete(this dts job has been ok for the last three years).
    However, after applying SP4, this DTS job now takes over 8 hours to complete.

    There are no other processes running on the box and the box is a high end Dell machine with 8 Gig of RAM.

    Any advice on this would be greatly appreciated.

    Bal

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What kind of DTS takes 4 hours in the first place?!
    What on earth are you trying to do?
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Another coffee spewing on the monitor moment

    Let me guess. It does transformations with activex

    And who thought up this brilliant process

    This is why I hat M$

    They should never try and make life easy, it always gets abused
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You should see the crap I'm re-writing today then Brett
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by georgev
    You should see the crap I'm re-writing today then Brett

    I would plead ignorance
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Do you have /AWE or other memory extensions enabled? SQL 2000 SP4 needs an extra hotfix, because of a problem accessing memory beyond 2GB.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Brett Kaiser
    I would plead ignorance
    If only it were that easy, eh?
    George
    Home | Blog

  8. #8
    Join Date
    Sep 2007
    Posts
    4
    I inherited the thing!

    It pulls in data from various sources and transforms it in SQL Tasks, no ActiveX components.

    We've tracked it down to AWE and that latching issue.
    We are applying the hotfixes in the attempt that this will resolve the problem.

    Cheers for all of your help chaps(inc the mickey takes!)

    Bal

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    SQL transform tasks should still not add up to 4 hours!
    Please don't apply a band-aid to this, fix it, I beg of you!

    Imagine the guy who inherits this piece of poop after you, eh?
    George
    Home | Blog

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    if you do transformations, then it can't do set based processing
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Sep 2007
    Posts
    4
    Good point!

    ....there are over 60 tranformations in that DTS package.
    The guy who wrote it now runs his own company and is doing very well for himself!!! I pity his clients though.
    This big thing is due to be replaced in the next few months, its a big load which takes data from dataflex, shoves the info into SQL tables and then the big job rips out all of the data using joins into more understandable tables.
    The whole thing takes over 9 hours....

    Bag of ****


    Cheers
    Bal

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sounds like you could manually analyze the data in less time
    George
    Home | Blog

  13. #13
    Join Date
    Sep 2007
    Posts
    4
    Brilliant!!!

    We have applied the hotfixs and the job is running even slower now.
    We will be looking at optimizing some queries. But the problem is still down to spid latches. Any advice on the latch issue would be helpful.

  14. #14
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by georgev
    What kind of DTS takes 4 hours in the first place?!?
    Do you find that strange? Gathering data from various ERP systems and putting them into an Analysis Server perhaps?

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    FOUR hours! I just find that very hard to believe.
    Balhar,
    I advise you run each step manually and see which ones take longer than a few minutes (or ok, maybe just the top 5-10 ), then report back with how long it takes and what the badger is trying to do. We can't help fix a problem blind, can we?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •