First published on MSDN on Feb 08, 2019
There are multiple ways to achieve this and there are also some
storage types
you should be aware
There is a good ...
Updated Mar 14, 2019
Version 2.0
Blog Post
JSBSF I am glad that worked out for you. So you didn't care about MAXSIZE, you just went after the databases with the largest amount of allocated space regardless of whether they were near their individual MAXSIZE or not? I could see where that is a reasonable approach for your use case.
We don't use SHRINK because of the massive overhead of rebuilding the indexes (TB-size databases, hundreds of tables) to fix the fragmentation it causes, and because we would likely need to allocate that space again too quickly, so we just make more space available.
Are you saying that sys.resource_stats.storage_in_megabytes is allocated storage? The documentation isn't clear to me.
I still need MAXSIZE because I am trying to look ahead to ensure I always have about 20% of free space.
