Unable to get size of Cosmos DB and SQL DB in Microsoft Fabric (Mirrored Databases)

[Shijoblesswin]

Hi,

I am working on an orphan recommendation feature in Microsoft Fabric. As part of this, I am extracting metadata and size information for the following resources:

Lakehouse
Warehouse
Cosmos DB Database (mirrored)
Azure SQL Database (mirrored)

I am able to retrieve the size for Lakehouse and Warehouse. However, I am not able to get the size of Cosmos DB and Azure SQL Database.

Since Cosmos DB and Azure SQL Database are mirrored in Fabric, they do not have a physical folder in OneLake. Because of this, the size is not available through OneLake storage metrics.

My questions are:

Is there any Fabric API or supported approach to extract the actual size of mirrored Cosmos DB and Azure SQL Database?

Or should the size be fetched directly from the source services, such as Cosmos DB and Azure SQL, using their native APIs?

[v-nmadadi-msft]

Hi @Shijoblesswin 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @Shijoblesswin 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[Shijoblesswin]

I checked the Azure portal as suggested. However, both the Cosmos DB and Azure SQL Database appear empty there, and I am not able to see any storage usage.

These databases were created and accessed through Microsoft Fabric as mirrored databases.

If they appear empty in the Azure portal, does that mean the data exists only within Fabric?

In that case, what would be the correct and supported approach to retrieve the actual storage size?

Is there any Fabric API available to get the storage size of mirrored databases?

[v-nmadadi-msft · Accepted solution]

Hi @Shijoblesswin ,

Please try the below steps to check the database file size details:

Navigate to the mirrored database in the Fabric portal.

Select View, then Source database. This action opens the Azure Cosmos DB data explorer with a read-only view of the source database.

Select New Query to open a query window.

Run the following SQL command:

SELECT
DB_NAME(database_id) AS DatabaseName,
name AS LogicalFileName,
type_desc AS FileType,
size * 8 / 1024 AS Size_MB
FROM sys.master_files
ORDER BY DatabaseName;

This query will return:

DatabaseName – Name of the database

LogicalFileName – Logical name of the data or log file

FileType – Whether it is a ROWS (data) file or LOG file

Size_MB – Current size of the file in MB

This will help you understand the data and log file sizes and evaluate overall database storage usage.


I hope this information helps. Please do let us know if you have any further queries.
Thank you

[v-nmadadi-msft]

Hi @Shijoblesswin  ,
Thanks for reaching out to the Microsoft Fabric Community forum.

Thank you @deborshi_nag for your suggestion.
Other supported approaches to view size of the database directly from Azure SQL database is by using the following APIs to measure the size of space allocated for databases and elastic pools:

• T-SQL: sys.resource_stats
• T-SQL: sys.elastic_pool_resource_stats

For more information please refer this document

Database file space management - Azure SQL Database | Microsoft Learn

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[deborshi_nag]

Hello @Shijoblesswin 

You're right, mirrored databases don't store data like Lakehouses or Warehouses. It does use it behind the scenes, but those storage areas are not exposed. 

The best way to know the size of these databases is to check portal for the source Azure resources. The actual size of the storage will be in the Storage blade. 

The Azure Monitor REST Metric API expose the Storage blade data for specific Azure resources. You can call that from a Python notebook in Fabric, assuming you have right permissions to the Azure resources. 

Cosmos DB 

GET https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/provi...

subscriptionId – your Azure subscription

resourceGroupName – RG containing the Cosmos DB account

accountName – your Cosmos DB account

Azure SQL Database

GET https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/provi...

[Shijoblesswin]

Thank you for your guidance.

I checked the Azure portal as suggested. However, both the Cosmos DB and Azure SQL Database appear empty there, and I am not able to see any storage usage.

These databases were created and accessed through Microsoft Fabric as mirrored databases.

If they appear empty in the Azure portal, does that mean the data exists only within Fabric?

In that case, what would be the correct and supported approach to retrieve the actual storage size?

Is there any Fabric API available to get the storage size of mirrored databases?