Non logged deletion of records?

[russellbcopeland]

Using SQL Server 2000

I have to delete appx 1 million records from a single table each day. The table contains appx 60 million records. This currently takes about 20 minutes which is way too long. The table will be growing by about 50 to 300% over the next 6 months. I need to delete these records FAST! Changing the structure of the database is not an option.

I have tried to find a way to delete records without logging but have not been able to find a method of doing this selectivley. (I can DROP or TRUNCATE but that wont do)

Any help would be greatly appreciated.

Thanks

 

[foxdev]

Are you confident that your WHERE clause is not the bottleneck? Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[tlbroadbent]

So far as I know, there is no way to selectively delete without logging. Here are some tips to help speed up deletes.

1- Make sure that one or more of the columns in the Where clause is indexed.

2- If possible to delete records during off hours, drop all indexes except the best index for selecting records.

3- If you can do so without affecting user access, lock the table before starting the delete process. If the table is locked, SQL Server will not use time and resources obtaining locks. You can do this by executing a Select statement like the following prior to starting the deletes.

SELECT top 1 'a' FROM MyTbl WITH (tablockx)

4- Run the delete in stages - perhaps 100,000 rows (or fewer) at a time. Enclose the table lock and delete in a transaction. Repeat the process until all desired rows are deleted. If you can't do this during off hours, you may want to delete in more, smaller batches.

Example:

Set Rowcount 100000
Drop index ....

While .... /* Loop control */
Begin
Begin Transaction
Select top 1 'a' FROM MyTbl With (tablockx)
Delete MyTbl Where ....
Commit Transaction
End

Create Index ....

In addition, if you can backup or truncate the transaction log after each stage, you'll see performance improvement because SQL Server will not need to continuously expand the log file.
Terry

http://members.home.net/tlbroadbent/index.htm