SQL Server database is too large because available space allocated 70% mdf space

Question

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

Answer 1

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.

Answer 2

What you need is to decide whether you can afford to have the free space allocated to this database or you would need to reclaim some disk space.

In case you need to reclaim, you will need to shrink the mdf file. You can do that in small chunks but remember it causes fragmentation.

How to shrink database files: https://learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file?view=sql-server-ver15

To shrink a data or log file

Connect to the Database Engine. From the Standard bar, click New Query.

Copy and paste the following example into the query window and click Execute. This example uses DBCC SHRINKFILE to shrink the size of a data file named DataFile1 in the UserDB database to 7 MB.

SQL

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

Best Practises for Data File shrink : https://littlekendra.com/2016/11/08/shrinking-sql-server-data-files-best-practices-and-why-it-sucks/

Answer 3

I've always found shrinking databases to be highly unpredictable as to whether or not it actually frees space. I've had to do it over and over again in the past to get it to work.

As others say, the best approach it to leave it, on the assumption that the space will need to be used eventually anyway.

However, if you do really need to shrink, there is an approach suggested by Brent Ozar in which you move the data to a new file an drop the old one. This approach requires some space overhead as you need to size out a new file.

Answer 4

How did you do the SHRINK? Using the GUI? It defaults to TRUNCATEONLY, which means that it doesn't move around any data. I.e., the last used page will limit how much smaller the file gets. In your case, it seems that the last extent in the database file was in use.

You need to do it without TRUNCATEONLY (so it shuffles the data towards the beginning of the file) and be prepared to wait for a very very log time.

So, consider whether it is really worth it. Here's an article on the subject I wrote a long time ago: https://karaszi.com/why-you-want-to-be-restrictive-with-shrink-of-database-files