SQL Server : database free space vs data file free space

Question

I don't understand well why the available space in database is 0% while in file is 98% available. Can you explain or advise? (SQL Server 2019, SSMS the newest, one datafile)

Answer 1

There are apparently errors in the calculations in SSMS.

Shrink Database dialog

Currently allocated space: show the allocated space for both data and log. Correct.

Available free space: show free space for only data, doesn't include log. Incorrect.

The calculating of the free space is off. It divides the full database size (including log) with the free space for the data file (only). Also, it presents the full database size (including log) and for free space, it only show free space for data.

Shrink File dialog

Referring to data file. This is inconsistent. For one of my databases, is show the correct info for the primary data and only file. For a different database (stack overflow 10 GB), it is way off with free space (both number and percentage). For the SO db, it shows only about 600 MB used, when in fact it is about 1.6 GB. My guess is that there is an overflow in a variable in the host language within SSMS causing a wraparound (if it were in TSQL you'd get an error instead)

I.e. disregard the GUI. Collect info from some more correct query/method (what Niels suggested for instance - I use my own sp_dbinfo for these things). and then use the DBCC SHRINK commands directly (preferably DBCC SHRINKFILE).

And, of course don't shrink unless you really really really need that disk space, it is a significant amount of disk space and you are willing to pay the price for shrinking.

Answer 2

The size of the database window is including the log file I believe. If you use the query below, you can check that.

SELECT name
,filename
,convert(DECIMAL(12, 2), round(a.size / 128.000 / 1024, 2)) AS FileSizeGB
,convert(DECIMAL(12, 2), round(fileproperty(a.name, 'SpaceUsed') / 128.000 / 1024, 2)) AS SpaceUsedGB
,convert(DECIMAL(12, 2), round((a.size - fileproperty(a.name, 'SpaceUsed')) / 128.000 / 1024, 2)) AS FreeSpaceGB
,CONVERT(DECIMAL(12, 2), ROUND((round((a.size - fileproperty(a.name, 'SpaceUsed')) / 128.000 / 1024, 2)) / (round(a.size / 128.000 / 1024, 2)) * 100, 2)) AS FreeSpacePerc
FROM dbo.sysfiles a

Not sure why the percentage is so off by the way, but what if you use sp_spaceused? Wonder what the unallocated space is there?

Regarding shrinking the database, you should not do that actually, unless you have a serious reason to do so. The only reason would actually be a test database where you are really sure the space will never be used again or if you delete a serious portion of the database. If you shrink the database and the database starts to grow again, then you will cause autogrowth events, causing your IO to temporarily freeze, causing performance issues, etc. Secondly it causes fragmentation and in order to fix that, your database will grow.

Here is an article from Brent Ozar regarding shrinking your database:

https://www.brentozar.com/archive/2017/12/whats-bad-shrinking-databases-dbcc-shrinkdatabase/