Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201

    Unanswered: Optimization Issue To Big

    Sql 2003

    Database Optimization not getting done.

    We have a weekly maintenance window and a database that we cannot get optimization to finish timely.

    In paticular we have a 70 gig table (indexes included) and the optimization fails because of log file filling up or just runs plain to long (8+ hours)

    The table is part of a 3rd party transactional database that cannot be motified.

    Is this just the way it has to be or our we missing something.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    a 70GB table?

    What's it used for and why do you need to reorg it?

    And why can't some of the data be moved off?
    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.

  3. #3
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Looking a creatation of an archieve process for some of the data on this table however need all the information to be accessible to the application. This will take 3rd party involvement not an easy thing.

    The reorganization is a good question one which i have asked and can get no valid answer for. My only guess is that this being a transactional system instant retrieve is required by the key value. This table is a EAV table.

    I server as a Database Analyist and not an Administator on this machine.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If diskspace is not an issue, can you get the data portion of the table into one filesystem on one set of physical disks, log (including tempdb log) onto another, tempdb data on a third, and the non-clustered indexes into a fourth? With minimum disk sizes these days around 70GB themselves, this may not be possible,m but it will help you, as you can then use the CREATE INDEX command with the SORT_IN_TEMPDB flag. You may want to play with that, anyway, even if you can only separate the data from tempdb on physical drives.

    EDIT: Oh, and take a full backup before you start, switch to simple recovery, reindex, switch back to FULL recovery, and take another full backup. The backups do not need to be exactly in the window, but it is nice.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what optimizations are you doing?

    If you are dropping and recreating indices, you might consider switching your recovery to Bulk Logged during your optimization routines.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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