hi all,

I have a issue with purging.

The tables in my database are partitioned based by month, so i want to purge the partition based on monthly basis, in total 8 months partitions each where each months size is approx 140GB

My questions are :

Is it possible to purge 8 months partition at a time ? meaning by issuing detach command in one script and executing it - if yes - then i would be having locking issues - which means the Async Index clean up might take very long to complete and my maintenance window time is just 4 hours.

As there are 20 tables with an average of 10 million pages in each one of them.

if tried to detach one partition at a time then it takes approx 3 hrs for Async Index cleanup to complete.

I would appreciate - if anyone of you can suggest a smart solution in order to speed up the process.

Nevertheless :

I thought of other alternatives too but not sure about the performance and how it would impact the system.

1) Emptying the tables on monthly basis by deleting with 10000 rows per execution, which would too tedious

In order to speed up the process is if try deleting 1 million rows then i might be filling up the logs.

In order to avoid log, i should try to alter the table with NOT LOGGED statement. (This change i cannot implement as per the company norms).

Then thought of doing online reorg.

I am running out of ideas, please suggest me a novel way to speed up this process.

Regards
Raoul