Slow Deletion

Question

Slow Deletion

Graeme100

SSCrazy Eights

Points: 8054
More actions
October 6, 2004 at 7:38 am

#85616

Hi Guys,
I have an audit log table with 300 million rows.
I would like to delete all those rows older than a certain date.
It is proving time consuming. I've decided to work on a day by day basis to start off with. I have deleted 5000 rows as a test and it took 11 minutes.
The server is not particularly powerful.
Is there anything that could cause slow deletions.
Regards..Graeme

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

jatighe SSChasing Mays Points: 638 More actions · Answer 1

You could try creating a temporary table or table variable. Insert into this table the log data you want to keep, then truncate (not delete) the original log data table before inserting back the data from the temp table.

Regards

Justin

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 2

Justin,

Thanks for that!

Yeah, that's what we are going to try.

I think that will be the least time consuming.

Thanks again..Graeme

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Graeme,

Justin is right it is a good solution.

Another solution is: since a Delete is a logged operation that puts entries in the transaction log for the active transaction iven if Simple recovery model is selected try shorter transactions that will delete like a hundred records at a time. Set OSQL job tha will run in a loop from a short VB program or that will run from a scheduler every minute that will select and delete something like TOP 100. It will probably run for some time, but it will not be your time, it will be ran as a job.

Yelena

Regards,Yelena Varsha

Hans Lindgren SSChampion Points: 10496 More actions · Answer 4

Try checking/optimizing your indexes, maybe your DELETE scanned your table?

The suggestion made by Justin will probably only e efficient if your amout of data-to-be-deleted is very large compared to total amout of data in the table.

//Hanslindgren

P.S This is the second time I am writing this answer so it is abit short. The webinterface AGAIN droppped my response making me having to rewrite it.

mhempenstall SSC Veteran Points: 276 More actions · Answer 5

I have found that putting the database in single user mode sometimes speeds up these sort of things as it can then avoid locking etc. Not sure if it will work in your case but might be worth a go if you can kick everybody out of the database.

ndeangelo SSC Enthusiast Points: 144 More actions · Answer 6

Yes, making sure it's non-logged will help a lot. Also, make sure there aren't any delete triggers on the table, which will cause it to go and do something else for every row that's deleted. It might help to turn off "Update statistics" while you're doing this as well.

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 7

In addidition to the other replies :

-If you are going to choose the "delete "-path, go for small transactions.

Declare @Counter int

Set @Counter = 1

Declare @TotCounter int

Set @TotCounter = 0

While @Counter > 0

Begin

Begin Transaction DelTop

Delete [dbo].[MyTable]

FROM (SELECT TOP 50000 Guid

from [dbo].[MyTable]

where tseventbegin < '2002-01-01 00:00:00' ) as SelTop

where [dbo].[MyTable].guid = Seltop.Guid -- guid =uniqueidentifier

set @Counter = @@rowcount

set @TotCounter = @TotCounter + @Counter

Commit Transaction DelTop

End

Print '--> MyTable # Rows Deleted : ' + str(@TotCounter)

go

- It is a good idea to turn off auto-stats, but remember to run sp-updatestats afterwards.

- if you disable triggers/constraints, keep in mind you are disableing them for all users in the db ! And remember to activate them afterwards. Keep an inventory which triggers/constraints you have disabled ! (maybe there are already some disabled and you don't want to activate them back just like that)

- inform users things may slow down/ be unavailable for a (long) while.

(5000 rows / 11 minutes is very slow !)

- run dbcc updateusage and sp-updatestats afterwards. (even with autostatistics on this is needed !)

Hope this helps

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 8

Thanks for all those suggestions guys.

This site is a great resource.

Graeme