Confusion on the Size of SQL Server Attached Local DB

Question

I've searched around the internet to check the size of my database and I found queries such as;

SELECT
    [name] AS [Filename],
    [size]/128.0 AS [Filesize],
    CAST(FILEPROPERTY([name],'SpaceUsed') AS int)/128.0 AS [UsedSpaceInMB],
    [size]/128.0 - CAST(FILEPROPERTY([name],'SpaceUsed') AS int)/128.0 AS [AvailableSpaceInMB],
    [physical_name] AS [Path]
FROM sys.database_files

This returns used space and available space of my database in terms of mb. The other one returns in terms of gb;

SELECT d.name, m.size * 8 / 1024 / 1024
FROM sys.master_files m 
JOIN sys.databases d ON d.database_id = m.database_id AND m.type = 0

But please pardon me because I am quite confused. I run this queries in the server explorer of visual studio since I have attached a local db for a program, the program is still in development.

What I would like to do is that, since it is said that SQL Server Express has a max of 10 gb, I would like to see that allocated size as well as the free space. Running those queries I only get a total of approximately 9 mb and it queries to the .mdf file (used space + available space)

I also have SQL Server Management Studio 2019 installed on my pc, can I use that to check the size as well?

My guesses is that the size is different from when it is deployed and that it will expand further? Or I am querying in the wrong place for the wrong thing.

Answer 1

Your queries should be accurate if they are run against the proper database. Database files also have a MAXSIZE and a FILEGROWTH factor, so your database size could be low now because you have very little data in it, but will grow as more data is added.

You can also use the GUI: In SSMS you can right click your database -> Reports -> Standard Reports -> Disk Usage.

Also, Right click your database -> Properties -> Select the "Files" from the "Select a page" section --- here you can check your InitialSize, Autogrowth/MaxSize properties.