Large-scale delete in SQL Server 2000

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

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!

 

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

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

 

If you want to get rid of everything , truncate.

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

 

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.

 

FORMAT C: