Delete SQL Server 2005 records without logging

Question

How to delete records from SQL Server 2005 tables without logging them into transaction logs.

I do not wish to log because once deleted, those records will never be needed again.

Currently the various deletes take too much of time. Are there any other options to improve performance of delete statements? I can not use truncate since there is a where clause needed.

Answer 1

It's easy:

DECLARE @BatchSize INT
SET @BatchSize = 100000

WHILE @BatchSize <> 0
BEGIN
    DELETE TOP (@BatchSize)
    FROM [dbo].[UnknownTable]
    SET @BatchSize = @@rowcount
END  

Answer 2

I think you are confusing the concept of a transaction log in the database. The transaction log's primary function isn't for restoring old rows -- it's job is to maintain the consistency of the database. All modifications go through the transaction log, and there is no way around that. That's a good thing. A transaction log is also used to make point-in-time backups and restores of the database, and is used when mirroring between two servers.

If you have deletes that are taking too much time, you should look in a couple of areas first.

1) Do you have any DELETE triggers that are firing on your tables? If so, those could be a source of slowness.

2) Has your DBA properly set up the database, at the bare minimum, keeping the transaction log and the data files on separate physical disks?

3) Do you have a lot of foreign keys which are getting checked? For example, if you have another table which references the table you're deleting from, the database server will check each delete against the referencing tables to make sure that the delete statement does not cause those other tables to become inconsistent.

4) Do you have too many indexes, or an otherwise high indexing burden on the table you're deleting from? Every deleted row will correspond to an entry in each index being deleted too, so be judicious about your use of indexes. Are your indexes being properly maintained?

5) Does it take a long time to seek out the rows you want to delete? If the WHERE clause on your DELETE statement is too costly, this will really slow down your deletes. Try temporarily changing your DELETE statement to a SELECT statement and see if that query runs fast. If it doesn't, you should optimize your SELECT statement either by editing it, restructuring your tables, or addindg the appropriate indexes. Then change the statement back to a DELETE. The performance should be significantly improved if your corresponding SELECT statement's execution improved during your optimization.

If you have a big batch job executing a great many deletes against your tables, you may want to temporarily disable your triggers, or you may want to drop and recreate your indexes and foreign keys after these big delete batches. That also could speed things up.