February 22, 2018 at 5:20 pm
Guys,
I am curious about how SQL works after purging data file . Per my understanding we need to shrink the data file after purging , Is it necessary ?
Logically after purging or deleting data , I would expect a certain amount of space available that can be used by upcoming transaction such as INSERT ( without shrinking)
Any feedback is much appreciate
Cheers
February 22, 2018 at 6:28 pm
WhiteLotus - Thursday, February 22, 2018 5:20 PMGuys,I am curious about how SQL works after purging data file . Per my understanding we need to shrink the data file after purging , Is it necessary ?
Logically after purging or deleting data , I would expect a certain amount of space available that can be used by upcoming transaction such as INSERT ( without shrinking)
Any feedback is much appreciate
Cheers
The data file will remain the same size as before the data purge. The only reason I can think of shrinking the data file is if the file is far larger than it needs to be or you're running out of space on the drive the file is on. Otherwise, you're going to have to rebuild the indexes on the file after the shrink, and it will just grow back. Shrinking the file would make sense if you were going to archive it.
February 22, 2018 at 6:37 pm
It's the typical "It depends."
If drive space is at a premium and other DBs are steadily growing and might end up needing the space, and if the purged DB isn't expected to use that space again, then yes, shrinking might be the best course of action.
Otherwise, you might as well just leave the file as it is and let the already allocated space be used. If the purged DB is just going to use that space again, then it's a bit silly to spend the IO and CPU to shrink the file down, and then incur more overhead to grow it out again.
Essentially, you need to answer two questions.
First, how important is it that space on the drive(s) in question be freed up for other files? If there's no need (and I mean "need", not "I get the warm fuzzies when free space is a bigger number") to free up the space for anything else, then it's probably best to just leave the database files as they are.
Second, how likely is it that the DB from which data was just purged will end up growing back out to its old size? If the growth was caused by some aberrant, one-off process that shouldn't ever happen again, then shrinking might (heavy emphasis on "might") not be a bad idea, if the answer to the first question was "Yes, I need that space for other things."
If it's likely that the workload for that DB will grow the DB back out to the size it was, then you should probably leave the file as is instead of incurring the IO and CPU overhead of a shrink just to incur even more overhead when the file grows back out (and this is even ignoring things like the potential workload introduced by a standard reindexing job going crazy because of the fragmentation introduced by the shrink, additional overhead if IFI isn't enabled, etc.).
If you do need the space AND it is likely the database from which the data was purged will grow back out to its old size, then you need to look at adding space so the workload(s) on the server can be accommodated without constant shrinks/regrowths.
Cheers!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply