June 8, 2012 at 6:59 am
I'm going to delete about 8 million recs from a table who has 27 milllion records. I had planned on looping and doing the delete 1000 at a time. It's an orders table so there will be traffic to the table while i'm doing the deletes however it will be off hours so the traffic should be minimal.
Is there a rule of thumb as far as how many deletes i should do in each iteration based on total number to delete and total size of the table?
Advice?
June 8, 2012 at 7:48 am
No, not really. The number of rows in each batch is really determined on two things:
1) possible lock escalation which will reduce concurrency
2) size limitations on the transaction log and log backup file
breaking up into smaller batches mitigates any concurrency issues.
Smaller batches spaced farther apart than the transaction log backup interval will minimize a bloated transaction log (file and backup)
The probability of survival is inversely proportional to the angle of arrival.
June 8, 2012 at 7:55 am
+1 sturner
however i did find on most of my configurations that 1000 is too small (as a rule of thumb) and that between 10k and 100k rows gave the best yield per iteration
depends on your width of data also - do you have large binary columns or nvarchar(max)??
might be best to switch to simple mode before doing a large delete and make sure you have a rollback position (create a snapshot or backup before your start)
MVDBA
June 8, 2012 at 8:07 am
is there a way to temporarily turn off the writting to trans log for this process?
June 8, 2012 at 8:11 am
BaldingLoopMan (6/8/2012)
is there a way to temporarily turn off the writting to trans log for this process?
yes, but it's not without pre-requisite actions - you must follow these in the exact order
0)stop transaction log backups
1)backup database
2)alter database x set recovery simple
3)perform deletes
4)alter database x set recovery full
5)backup database
6)re-start t-log backups
MVDBA
June 8, 2012 at 8:24 am
BaldingLoopMan (6/8/2012)
is there a way to temporarily turn off the writting to trans log for this process?
everything is written to the transaction log you cannot stop that, it all depends on your recovery model as to what happens to the transaction log after the transaction has finished.
the option Mike has given will make the log as re-usable as soon as the transaction is finished so you dont end up with a massive log at the end of the delete
eg, if deleting 1000 rows in 10 seconds causes a 10MB transaction log, and your in simple recovery, your transaction log will only be 10MB, due to each 1000 row delete starting from the beginning of the log every time (overwriting)
using same values above, if your in full recovery and you backup the tx log every minute, you have a 60MB maximum log, 15 minute backups 900MB log file, due to each delete starting from the end of the log every time (appending)
June 8, 2012 at 8:27 am
anthony.green (6/8/2012)
BaldingLoopMan (6/8/2012)
is there a way to temporarily turn off the writting to trans log for this process?everything is written to the transaction log you cannot stop that, it all depends on your recovery model as to what happens to the transaction log after the transaction has finished.
the option Mike has given will make the log as re-usable as soon as the transaction is finished so you dont end up with a massive log at the end of the delete
eg, if deleting 1000 rows in 10 seconds causes a 10MB transaction log, and your in simple recovery, your transaction log will only be 10MB, due to each 1000 row delete starting from the beginning of the log every time (overwriting)
using same values above, if your in full recovery and you backup the tx log every minute, you have a 60MB maximum log, 15 minute backups 900MB log file, due to each delete starting from the end of the log every time (appending)
true.. it wont turn of writes - just limit the total size (my internal auto-translation read "turn off" as "limit size") 🙂
MVDBA
June 8, 2012 at 8:40 am
michael vessey (6/8/2012)
BaldingLoopMan (6/8/2012)
is there a way to temporarily turn off the writting to trans log for this process?yes, but it's not without pre-requisite actions - you must follow these in the exact order
0)stop transaction log backups
1)backup database
2)alter database x set recovery simple
3)perform deletes
4)alter database x set recovery full
5)backup database
6)re-start t-log backups
Actually, no, you can't turn off logging. Even when using the simple recovery model, all transactions are still logged.
June 8, 2012 at 8:41 am
BaldingLoopMan (6/8/2012)
I'm going to delete about 8 million recs from a table who has 27 milllion records. I had planned on looping and doing the delete 1000 at a time. It's an orders table so there will be traffic to the table while i'm doing the deletes however it will be off hours so the traffic should be minimal.Is there a rule of thumb as far as how many deletes i should do in each iteration based on total number to delete and total size of the table?
Advice?
You may want to read this article: http://qa.sqlservercentral.com/articles/T-SQL/67898/.
June 8, 2012 at 12:24 pm
very cool thanks guys. I'm going to integrate into this process a way to backup the trans logs if it goes above 50% free space.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply