We have about 10 tables in production that store information on sessions and logging. We have rules to delete these tables. i.e. Table1 -> need to keep records within 180 days, Table2-> within 7 days, Table 3 and 4 have master detail relationship and need to keep only past 180 days and so on.

Table3, table5 have about 40 million records. All others have less than 100000 records.
We want to write a script that runs every month to clean this up.

Here’s what I thought. Make this table driven i.e. keep count of records that need to be deleted, date, datecondition in a table and write a cursor and dynamic sql to delete 10,000 records based on date. Note: none of the tables have index on dates and I am not allowed to add indexes.

What are your suggestions so that end users won’t see the slowness and are there more efficient ways.