1

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:

  1. Using a while loop, it removed data older than three months
  2. Shrinks the database, if its size is above some threshold
  3. 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?

  • I think that article has all your answers. Do you really need that 4GB of space? If not, don't shrink. It is bad for all the reasons in the article. Reindexing, however, may still be beneficial depending on how you actually use this environment. – LowlyDBA Apr 17 '18 at 12:42
  • 4
    Dumb question -- how much is the database going to grow when you rebuild your largest index? – Erik Darling Apr 17 '18 at 12:42
  • @LowlyDBA - the problem is that I have to ensure that the database files (actual data, no log) does not go beyond 10GB, otherwise it will stop due to Express' limitations. It just happened to gain 4GB for this particular job run. – Alexei Apr 17 '18 at 13:03
  • @sp_BlitzErik - disk space is not a problem (if this is implied by your question). The problem is to ensure data files < 10GB (express edition). – Alexei Apr 17 '18 at 13:05
9

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?

  • Yes, the requirement goal is correct and I should change my job to reflect that. However, my goal is to ensure I have data < 10GB. Since the job removes a significant part of data (old data, old custom logs) I expect not to get anywhere the initial size. – Alexei Apr 17 '18 at 13:09
  • Also, amassing more than 10GB in that period is extremely improbable since this is a fully controlled test environment (only a few users). – Alexei Apr 17 '18 at 13:11
  • I agree with @aaron-bertrand though, if you leave this go then it will just grow to 9gb again. You're better off setting a file size limit, so you don't go over, setting an alert when you're at 9gb (to clean it up), and leaving it the size it is. That way it doesn't take extra work growing, and shrinking. You really get nothing other than some temporary space until it grows again. I would rebuild the indexes because the data significantly changes. – crucible Apr 18 '18 at 4:19
1

Since this is a test environment, why couldn't you use SQL 2017 Developer? Then, you won't be constrained by the 10GB limit and thus less of a need for deleting records.

  • Yes, it is true that I could use the developer edition. – Alexei May 29 '18 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.