1

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)

Shrink Database

Shrink Data file

3
  • What does the log file say for allocates vs free space?
    – Aaron
    May 25, 2020 at 19:06
  • Also how many data files do you have and what is the default file group?
    – Aaron
    May 25, 2020 at 19:08
  • There is only one data file in primary group. The recovery model is full and log file is <1GB. May 27, 2020 at 7:14

2 Answers 2

2

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.

1
  • 'Available free space: show free space for only data, doesn't include log. Incorrect'. Ah! I did not know that. Thanks Tibor. As you also suggested, I only use queries to get that information. For me to be sure there is no bug in the GUI. I think that far more DBAs use queries, so bugs in there would be reported much more frequent is my personal way of thinking. May 29, 2020 at 9:07
0

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/

3
  • Hello, Thank you for hint, DB file size is: 313.21 Space Used is: 313.11 free space in DB: 0.10 Log file size: 0.31 space Used in LOG: 0.08 free space in LOG: 0.23 May 27, 2020 at 7:15
  • sp_spaceused returns: Database size: 321050 MB, unallocated space: 109 MB Reserved: 328316376 KB, Dat: 328207472 KB, Index Size: 39048 KB, Unused: 69856 KB May 27, 2020 at 7:21
  • Another Idea. I have check the table sizes in DB, then I check the design of the biggest table (mostly all space is consumed by one table) and query the table. I saw there is one filed as comment (ntext), which is mostly empty. As ntext use quite a lot allocation, it might be the case why table is so big with emty data, which reflect the database file size is consumed by empty space while database it self is fully occupied? Can you support this forensics analysis by some article? I did not find something usefull. May 27, 2020 at 7:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.