My database is 825 GB on disk, but unallocated space is about 500 GB (825GB * 55%).
After I shrank the database and files in SQL Server Management Studio, it had no improvement to reclaim the total .mdf file size. But I observed the unavailable space increased to 600 GB, total size of the .mdf file still was 825 GB (before shrink, I rebuilt the the index of tables which used to full text index search).
Do I have approach or configuration to reduce or reclaim the unallocated space (available space)?
Some detail:
- Recovery mode: Simple
- SQL Server 2016 CU15
Best Answer
The "unused space" is reserved by extents of pages which are maintained to be allocated to your data as your Tables and Indexes grow. The reason it's as big as it is currently is because you had a growth event at some point in the database that caused the MDF to grow.
General recommendation is to not shrink the file back down because it's likely that unallocated (and even unused) space will become allocated and used, and growth operations are heavy and affect performance. (There's also potential risk of data corruption as a result, when doing it to the MDF.)
It is not uncommon to have about 50% of your drive space in Unallocated. You can read more about Unallocated Space here. When SSMS calls it Space Available, it's already reserved by SQL Server but available to become allocated as needed.