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
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.
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
or try if you want to see the space available
exec sp_msforeachdb "use [?]; exec sp_spaceused"
Try out the DatabaseSizeSP stored procedure – it will show file sizes and space used information for each database
It can be found here:
Deploy to any database and run it
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 🙂