DELETE without logging

Hi all,
Is there a way to execute a DELETE sentence with a WHERE condition (cannot use TRUNCATE TABLE) without logging in order to execute a fast deletion of records?
Thanks in advance.
God Bless.Q1 Is there a way to execute a DELETE statement with a WHERE clause (cannot use TRUNCATE TABLE) without logging?


A1 No. (Not in any kind of production setting. Also, note that truncations are not logged on a row by row basis.)

Q2 Is there a way to execute a DELETE statement with a WHERE clause in order to execute a "faster" deletion of records?


A2 Sometimes, yes. Generally optimizing large scale deletions involves on the data, indexing, the schema, and the environment. Sometimes it is a matter or having a suitable index the system can take advantage of; sometimes it may be a matter of reducing concurrency locking issues; sometimes it may be a matter of dropping excessive indexes (and later recreating needed ones); often several such factors may be involved.

Delete records without logging

Hello,
I have one table that has 59 million records now i need to delete part of
this table (near 55million records)
Is it possible to do this operation without being logged anything in the
transaction log?
When i try to perform my delete, *.ldf starts growing and the operation
stops since i only have 300MB available in the drive.
Thanks and best regards
BCP out the 5Million rows that you want to keep and then truncate the table
and BCP them back in. Otherwise you should delete in small batches of say
10 to 100 thousand at a time.
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:CBDC02AB-DC72-4653-A2CD-53B3C32076F2@.microsoft.com...
> Hello,
> I have one table that has 59 million records now i need to delete part of
> this table (near 55million records)
> Is it possible to do this operation without being logged anything in the
> transaction log?
> When i try to perform my delete, *.ldf starts growing and the operation
> stops since i only have 300MB available in the drive.
> Thanks and best regards

Delete records without logging

Hello,
I have one table that has 59 million records now i need to delete part of
this table (near 55million records)
Is it possible to do this operation without being logged anything in the
transaction log?
When i try to perform my delete, *.ldf starts growing and the operation
stops since i only have 300MB available in the drive.
Thanks and best regardsBCP out the 5Million rows that you want to keep and then truncate the table
and BCP them back in. Otherwise you should delete in small batches of say
10 to 100 thousand at a time.
--
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:CBDC02AB-DC72-4653-A2CD-53B3C32076F2@.microsoft.com...
> Hello,
> I have one table that has 59 million records now i need to delete part of
> this table (near 55million records)
> Is it possible to do this operation without being logged anything in the
> transaction log?
> When i try to perform my delete, *.ldf starts growing and the operation
> stops since i only have 300MB available in the drive.
> Thanks and best regards

Delete records without logging

Hello,
I have one table that has 59 million records now i need to delete part of
this table (near 55million records)
Is it possible to do this operation without being logged anything in the
transaction log?
When i try to perform my delete, *.ldf starts growing and the operation
stops since i only have 300MB available in the drive.
Thanks and best regardsBCP out the 5Million rows that you want to keep and then truncate the table
and BCP them back in. Otherwise you should delete in small batches of say
10 to 100 thousand at a time.
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:CBDC02AB-DC72-4653-A2CD-53B3C32076F2@.microsoft.com...
> Hello,
> I have one table that has 59 million records now i need to delete part of
> this table (near 55million records)
> Is it possible to do this operation without being logged anything in the
> transaction log?
> When i try to perform my delete, *.ldf starts growing and the operation
> stops since i only have 300MB available in the drive.
> Thanks and best regards