Posted by helmut woess on 11/05/05 12:10

Am 4 Nov 2005 19:56:22 -0800 schrieb harish:

> 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

In this special case i would think about using a table per week. There is
no faster way then DROP/CREATE or maybe TRUNCATE. You have to change a lot
in the way you work with this data, but you have UNION and maybe you can
use VIEWS.
Or you use a big Solid State Disk for your database :-))

bye,
Helmut

[Back to original message]