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
- moving 2 years of data from main table to another table from batchwise
- truncate the main table
- 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