Posted by Tony Rogerson on 11/05/05 09:19

Hi Harish,

You should look at partitioning, keep a cycle the partitions and simply
CREATE TABLE and DROP TABLE the new partitions, that way you won't have to
do any logging.

Tony

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"harish" <harish.prabhala@gmail.com> wrote in message
news:1131162982.103343.268120@o13g2000cwo.googlegroups.com...
> 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
>

[Back to original message]