SQLTeam.com | Weblogs | Forums

Drop or Truncate a particular partition


#1

Hi ,

i am using SQL2008R2 version. one of my table (Partition ) contains huge amount of data like 800 GB now i am trying to clean the data. and i want keep only 2 years of data from 2016 to till date ( contains 80 millions of records )remaining old data is not required.

i tried following way

  1. moving 2 years of data from main table to another table from batchwise
  2. truncate the main table
  3. insert into main table from another table through batchwise

but its taking longtime and kill the performance in production env.

and i saw some forums that we can delete/truncate the partition id.

please help me on this


#2

Truncating individual partitions is a SQL2016+ feature. You could put the DB into Bulk Logged recovery mode, delete the data (might be worth doing a bit at a time) and then switch back to Full logging. That will minimize log usage without breaking your backup chain.