I have a table that has 86 GB, I want to purge it and keep only one-year data. After purging, about 50 GB removal.
1. My deletion operation is committing every 1000 rows to delete till to the end. I want to know if the deletion process itself will cause any or huge performance issue?
2. After deletion process, to reclaim the disk space, I need to shrink the database. Is it a way only shrink a table or entire database?
3. During the shrinking database process, I want to know if the process cause any or huge performance issue? how to avoid or minimize the impact?
4. During the shrinking database process, I want to know if I need to stop all the backup jobs ( Full, Diff, Log)?
5. After the shrinking process, I need to rebuild all indexes in the database. I want to know if the process will cause any or huge performance issue? how to avoid or minimize the impact?
6. If I run the purge, shrink, rebuild indexes on a stage machine, then import the table into production, then swap it with the original table, then drop the original 86 GB table. Dropping a big table will automatically reclaim a disk space. Is this a better way to do this way than directly running deletion, shrink and rebuild index on production?
My server is mssql 2012