Delete large amount of records

3 Feb 2006 4:55 PM

mecn

Hi,

I need to delete large amount of record from sql2k table weekly.
My question is that there is any way that I could delete them pypassing sql
log file.
Delete * from table1 where year(createdate) < '02'

Thanks,

3 Feb 2006 6:21 PM

Dean

no, DELETE is a logged operation. if the majority of rows in the table is to
be deleted, it might be faster to move the remaining rows into a new table,
truncate the original table (truncate is non-logged) and then re-insert, or
drop the original table alltogether and rename the new table. another
approach would be to delete smaller portions of data in a loop, one month or
one week at the time.

dean

Show quote

"mecn" <mecn2***@yahoo.com> wrote in message
news:eBm8nKOKGHA.1312@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I need to delete large amount of record from sql2k table weekly.
> My question is that there is any way that I could delete them pypassing
> sql log file.
> Delete * from table1 where year(createdate) < '02'
>
> Thanks,
>

3 Feb 2006 7:12 PM

mecn

Thanks,
Dean

Show quote

"Dean" <dvitner@nospam.gmail.com> wrote in message
news:OK5F05OKGHA.604@TK2MSFTNGP14.phx.gbl...
> no, DELETE is a logged operation. if the majority of rows in the table is
> to be deleted, it might be faster to move the remaining rows into a new
> table, truncate the original table (truncate is non-logged) and then
> re-insert, or drop the original table alltogether and rename the new
> table. another approach would be to delete smaller portions of data in a
> loop, one month or one week at the time.
>
> dean
>
> "mecn" <mecn2***@yahoo.com> wrote in message
> news:eBm8nKOKGHA.1312@TK2MSFTNGP09.phx.gbl...
>> Hi,
>>
>> I need to delete large amount of record from sql2k table weekly.
>> My question is that there is any way that I could delete them pypassing
>> sql log file.
>> Delete * from table1 where year(createdate) < '02'
>>
>> Thanks,
>>
>
>