Large-scale delete in SQL Server 2000

[Gaz 45]

Alright then lads & ladies, anyone have any tips on carrying out large scale deletes (millions of rows) without creating a transaction log bigger than Ayer's rock?

Let's have a discussion!

[hbroomhall]

What percentage of the total number of rows is that?

If this is high consider doing a SELECT INTO a temp table with the oposite logic, truncate the original, and copy back.

But don't be nasty to the transaction log. That's there so that you can recover from horrors. Treat it nice!

Harry.

[supag33k]

Originally Posted by hbroomhall

What percentage of the total number of rows is that?

If this is high consider doing a SELECT INTO a temp table with the oposite logic, truncate the original, and copy back.

But don't be nasty to the transaction log. That's there so that you can recover from horrors. Treat it nice!

Harry.

Yes a very good point about opposite log Harry!

Remeber the difference between logged operations such as DELETE and non logged such as Truncate with regards to the transaction log.

Note that Truncate takes out all rows!

http://www.sql-server-performance.com/misc_tips.asp

[zardoz]

If you want to get rid of everything , truncate.

Otherwise, do your deletes, then do a transaction log backup.
Problem solved.

[Crito]

Not to nit pick, but some people make a big deal about it. Truncate table is technically a minimally logged operation. It doesn't work on individual rows and it won't fire triggers, like a fully logged operation (delete), but it's not quite a non-logged operation either.

[JonnyMX]

FORMAT C: