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.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?|||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.|||Like as in

http://en.wikipedia.org/wiki/Entity-Attribute-Value_model

I hate that model|||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.|||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.