Sample Header Ad - 728x90

Best practise for reducing database file size during migration to new server

4 votes
1 answer
359 views
**Introduction** I have been reading many articles about deleting from a databases, and the perils of using the shrink command. However, I have an opportunity to move a database to a new server with a big outage window, so do want to give the database a reset and size reduction (to increase performance and save cost in hosted environment). **Background** * Existing SQL Server is 2016 Web Edition. * New SQL Server 2022 Standard Edition. * Database size ~120GB. * A few large tables make up the bulk of that as well as indexes on those large tables. * I want to focus just on the database size reduction. I'm comforable with the other parts of creating a new server. **Proposed process** _Before outage_ 1. Run stored procedures which use DELETE FROM syntax. I have tested these carefully. They batch the deletes and will be run over a few days. 2. Monitor the log files and log disk to double check there is enough space. 3. Ensure delete processing is done by the time we get to the outage window. _During outage_ 4. Take a full backup of the database and put in a safe place. 5. Drop the largest indexes (there around about 3 or 4 that I will target). 6. Put the database into simple recovery mode. 7. Shrink the log and database files as much as possible. 8. Backup the database. 9. Copy the backup file to the new server. 10. Restore the database to correct file locations 11. Apply the indexes which were removed. 12. Rebuild all indexes. 13. Update statistics. 14. Put database back to full recovery model. 15. Testing and restarting applications
Asked by ErikEV (77 rep)
Apr 7, 2025, 11:59 PM
Last activity: Apr 8, 2025, 11:49 AM