I have a test environment running on SQL Server 2017 Express that uses only recent data (a job removes data older than three months once per month).
The job does the following:
- Using a while loop, it removed data older than three months
- Shrinks the database, if its size is above some threshold
- Reindexes all tables
This article describes how awful is to shrink the database due to index fragmentation and why reindexing also requires extra space.
Last run started from a 9GB database. Deleting tooks 3-4 minutes and reindexing about 30 seconds. The database was reduced to about 5GB.
Since this is a test environment, I can afford this downtime without any issues.
Question: Is it bad to shrink + reindex after removing a significant part of data?