SQL Server – Is there a way to query the specific Size and Space available settings for each Database, including system databases on an instance?

All we need is an easy explanation of the problem, so here it is.

I am trying to query the two default database properties as seen in the picture below for all databases on an instance, including system databases.

I’ve used various scripts such as the following, but I’m not sure exactly where data for those two fields are stored.

SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id

SQL Server - Is there a way to query the specific Size and Space available settings for each Database, including system databases on an instance?

Thank you for your help.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Just check the properties of the model database – everything else being equal a new database is created using the model database as a template.
If you want to change the default size, resize your model database – job done.
Note however if the database creation script supplies a specific value that will take precedence.

re-read the question including your title and I think you want the results of running

exec sp_helpdb

or try if you want to see the space available

exec sp_msforeachdb "use [?]; exec sp_spaceused"

Method 2

Try out the DatabaseSizeSP stored procedure – it will show file sizes and space used information for each database

It can be found here:

https://github.com/aleksey-vitsko/Database-Administrator-Tools

Deploy to any database and run it

SQL Server - Is there a way to query the specific Size and Space available settings for each Database, including system databases on an instance?

Method 3

Ah, I had misread the question as wanting to know the free space the DBs had to expand into on the storage system, not the free space amongst that already allocated, so this answer does not exactly cover the question.

I doubt in a truly reliable manner. You would have to get the figures per file not per DB as each file making up the database could be on a different volume, and you need to account for the fact that some files from multiple DBs could be on the same volume. Also if you are using network attached storage any space available figure could be unreliable both because the volume size could be dynamic and because there are other uses applying pressure on available space at any given time.

I assume the figure presented in SSMS is coming from it looking at the filesystem instead of being read from data held in system tables/views.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags:

Leave a Reply