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

Namachivayam Ramesh 10 Reputation points
2023-05-31T04:31:19.99+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,272 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,603 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Namachivayam Ramesh 10 Reputation points
    2023-06-01T09:35:26.0566667+00:00

    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

    2 people found this answer helpful.
    0 comments No comments

  2. Olaf Helper 42,926 Reputation points
    2023-05-31T06:15:22.5666667+00:00

    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
    

  3. Erland Sommarskog 106K Reputation points MVP
    2023-05-31T21:51:46.7766667+00:00

    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.