We have a SQL Server installed. We have a 500GB drive for the database. Currently, we have 95 GB free on this drive. When I go into the database, and look at the Shrink File toolset, it shows the database at 410000 MB and the Available Free Space at 4500 MB, but the drive itself has 95 GB free. The files are set to 64MB Auto growth with a max size of Unlimited.
I take a backup of the files, then run DBCC SHRINKFILE() commands, but nothing is freed up. The size of the database makes sense at 400GB with 95GB free, but why does the DB think there is no available space and how do I fix this?
What's being reported is ONLY what's available in the current database file, SQL Server doesn't count the free space on the drive as part of the database. If you moved the database to a drive with only 415GB, you'd see exactly the same behavior. IF you manually grew the database by another 10GB, you would then see 14.5GB free when you checked the free space.
Alternatively, if the database has to grow to accomodate new data being inserted, the available free space will change (keep in mind, too, 64MB growth is on the small side and a grow operation could negatively impact performance while it's going on, I tend to use 1024MB (1GB) for growth.)
So, really, there's nothing TO fix, SQL is behaving normally.
May 14, 2026 at 2:22 pm
OH. I see what you mean. I mistakenly assumed because I had the growth to unlimited, it would use the available drive space, but I need to change the Size of the allocated database itself inside the properties (I know I can do a T-SQL for this, but just using the GUI SSMS now). So I needed to go into Database --> Properties --> Files and increate the Size (MB) column to account for the drive space. I guess I just believed this grow since the Autogrowth was set to Unlimited, but it only grows unlimited to the setting you have here. Thank you for correcting me on this. I do appreciate it.
May 14, 2026 at 2:37 pm
You're quite welcome.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply