Prev: Multi table insert; does it do a single pass over the source?
Next: Adjusting the IDENTITY next-value counter in version 7?
From: shekhar udb on 11 Aug 2010 22:56
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



 | 
Pages: 1
Prev: Multi table insert; does it do a single pass over the source?
Next: Adjusting the IDENTITY next-value counter in version 7?