I need to delete about 75+ million rows from a table everyday that contains around 3.5 billions of record.
Database recovery mode is simple, I have writen a code that deletes 15.000 rows in a while condition until all 75M records is deleted. (i use batch delete due to log file grow) However, with current deletion speed it looks like it will take at least 5 days, which means that amount of data required to be deleted is multiply faster than my deletion speed.
Basically what i’m trying to do is summarizing (in another table) and deleting data older than 2 months. There is no update operation in that table, only insert and delete.
I have an enterprise edition of MSSQL 2017
Any suggestions will be welcome.