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?
Best Answer
Well, ask yourself: What is the point of shrinking, if you know the reindex (and any other data changes, really) will expand the file again after the shrink?
Even if the reindex doesn't bring it back up to 9GB, if the database is going to grow in the future, I see absolutely zero value in reclaiming some of that disk space temporarily. What will you do with that space until the database has to grown again? Lease it out and then evict them? This is like a restaurant disassembling a table every time a customer gets up and leaves, only to reassemble it when a new customer arrives.
In your situation (where you need to keep the data file under 10GB because of the license you chose and no other reason), there is no point going down to 5GB just to grow back to 9GB again. I would rather have some active monitor that warns you when the data file is getting back up into the 9GB space again (or just keep it at 9GB!) and the space used is actually approaching 9GB.
Instead of thinking about it as a "keep the last three months" requirement, think of it as "keep 9GB of data" requirement. Which you'll have to revisit as your data volumes per time period (day/week/whatever) go up anyway. What happens when you amass more than 10GB of data inside of three months?