Any suggestion to programmatically (C#) read the available DatabaseSize from SQL Server 2022

Question

We were using SMO DataFile.AvailableSpace to figure out the free space left in the Database and DataFile.UsedSpace to figure out the space used by the Database. When the Database size is up-to 3994MB, then these two fields return proper value, when the DatabaseSize is set from 3995MB onwards, it returns invalid value.

https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.datafile.availablespace?view=sql-smo-preview

https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.datafile.usedspace?view=sql-smo-preview

There are some ideas about using the stored procedure sp_spaceused

Do we have any other suggestion using SMO to retrieve the database used and remaining size?

Answer 1

We identified the Microsoft SQL SMO is unable to retrieve the used and free database size in SQL Server 2019 version 2019.0150.2000.05, after upgrading to KB5021124, the issue is resolved.

https://support.microsoft.com/en-gb/topic/kb5021124-description-of-the-security-update-for-sql-server-2019-cu18-february-14-2023-cfb75a0a-33dc-4e05-8645-4cf16fcec049

Answer 2

it returns invalid value.

What for invalid values; invalid in which way?

You can simply query the database size (incl. log) with

select db.name as databasename, sum((mf.size * 8.094 / 1024)) as SizeMB
from sys.databases as db
     inner join
	 sys.master_files as mf
	     on db.database_id = mf.database_id
group by db.name
order by db.name

Answer 3

Sounds like a bug to me, but I may be misunderstanding. Would it be possible for you to compose a repro that demonstrates the issue. This repro would create the database with the critical sizes and then run the SMO calls that return the incorrect values.