table purging impact

wasabi3689
wasabi3689 used Ask the Experts™
on
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.

Questions

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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Co-Founder and Chief Architect
Top Expert 2016
Commented:
If you're doing massive deletion, I would do it during the maintenance window to avoid issues, especially if you're constraint aware.

You're shrinking files (which shouldn't be done regularly but sounds like this is just an archival), so if the table is in a separate file group you can just shrink that, otherwise you'd be doing the whole database.  (I often split larger tables out into separate file groups with different higher growth rates.)

Shrinking the files from my experience does degrade performance quite a bit while it's running.  I would only do that during a maintenance window.

Rebuilding the indexes will make the tables inaccessible, maintenance window.

For #6, you still are exposed to fragmentation which is what you are trying to avoid, and the indexes would still need to be rebuilt.

That being said, why do you need to shrink the space?  If its going to grow back to that volume anyways?

Author

Commented:
We only want to keep 1 year data. Now it's 10 year data. We want to reclaim the space after shrinking.
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017
Commented:
Wasabi, your steps are all correct.
You might want to process even more than chunks of 1000 records if you can schedule the purge process to run during a less active period (nights and weekends for example) as this will cause performance issues.
Also turn off Full and Diff backups. Transaction log backups should be run without issues if you have them running every 15 minutes or so.
Just perform the shrink after purge ALL records. Do not shrinking in the middle of the process. Space is better claimed when you know that's the final data that you want to keep. And after shrinking the database you need to rebuild the indexes. This two tasks need to be performed also during a period with no activity in the database as they have an huge impact in the performance.
Don't forget to re enable the backups after the process completes successfully.
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
Wasabi, did you have any other questions or did this answer for you?
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
Recommendation to close this question by accepting the above comments as solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial