January 13, 2021
OK, first of all. I do not need to explain the difference between truncate and delete. You most probably already knew it. I don't think truncate will help at this moment.
My situation here is my table has almost 90M records and this records was a monthly data snapshot for a year. I need to remove records from Jan to Jun and the rest will remain.
Let summarize this;
Objective : To remove past 6 months records which approximately 45M ++ records to be deleted
Constraint : My disk utilization almost 90% and insert/delete command both will generate log which will make log file increase drastically during the insert/delete operation.
What I do?
As I mentioned earlier, my data is a monthly snapshot data. I cannot simply execute delete command with filtering condition where date < '2020-07-01' right? If you do this, your log file with increase skyrocketly.
I prepared 6 delete command lines, each specifically will delete data for only 1 month, in sequence. And to handle the log? I will put extra DBCC command so that it will shrink the log file before another delete operation taking place.
DBCC SHRINKFILE (your_log_file_name, 1); * 1 means shrink the file to 1MB
Before that, I need to make sure that the database recovery log are set to Simple instead of Full.
delete from my_table where date < '2020-02-01';
DBCC SHRINKFILE (your_log_file_name, 1);
delete from my_table where date < '2020-03-01';
DBCC SHRINKFILE (your_log_file_name, 1);
delete from my_table where date < '2020-04-01';
DBCC SHRINKFILE (your_log_file_name, 1);
delete from my_table where date < '2020-05-01';
DBCC SHRINKFILE (your_log_file_name, 1);
delete from my_table where date < '2020-06-01';
DBCC SHRINKFILE (your_log_file_name, 1);
delete from my_table where date < '2020-07-01';
DBCC SHRINKFILE (your_log_file_name, 1);
That approach works for me and I believe it should work for you too. I you have suggestion or comment, do comment me below.
Thanks!
No comments:
Post a Comment