sql server misc: DELETING 100 million from a table weekly SQl SERVER 2000 -- sql server misc

DELETING 100 million from a table weekly SQl SERVER 2000

harish
11/4/2005 7:46:57 PM

sql server misc: DELETING 100 million from a table weekly SQl SERVER 2000

Hi All

We have a table in SQL SERVER 2000 which has about 250 million records
and this will be growing by 100 million every week. At a time the table
should contain just 13 weeks of data. when the 14th week data needs to
be loaded the first week's data has to be deleted.

And this deletes 100 million every week, since the delete is taking lot
of transaction log space the job is not successful.

Can you please help with what are the approaches we can take to fix
this problem?

Performance and transaction log are the issues we are facing. We tried
deletion in steps too but that also is taking time. What are the
different ways we can address this quickly.

Please reply at the earliest.

Thanks
Harish

Re: DELETING 100 million from a table weekly SQl SERVER 2000

Danny
11/6/2005 12:00:00 AM

The answer is clear make it a partitioned view. Partition on the date
component. Each week start a new table, drop the oldest table and alter
your view.

[quoted text, click to view]