Dedicated SQL pool storage capacity and database details

Hi , hope doing well , To answer your query please check below

To find out how much storage has been occupied and how much is left in the Dedicated SQL pool, you'll typically need access to the SQL server where the pool is hosted. You can run SQL queries to retrieve this information.

The specifics might vary based on the SQL technology being used (e.g., Azure SQL Data Warehouse, Synapse Analytics, etc.). However, here's a generic query that could give you an idea of how to get the information

Querying the sys.dm_db_partition_stats view:

• Connect to your Azure Synapse Analytics database using SQL Server Management Studio or any SQL client.
• Run the following query to get the used storage in your SQL pool:

SELECT
    SUM(used_page_count) * 8.0 / 1024 AS UsedStorageInGB
FROM
    sys.dm_db_partition_stats;

Calculating remaining storage:

• Subtract the used storage from the total allocated storage to find the remaining storage.
• The total storage capacity allocated for your Dedicated SQL pool should be provided by the client as 5 TB.

Please accept answer if it helps

Hi Deepanshu,

Thank you for reply,
I have tried same query and got encountered with below error
Catalog view 'dm_db_partition_stats' is not supported in this version.
which version is this error for?
I am using SQL server management studio 18

The error message "Catalog view 'dm_db_partition_stats' is not supported in this version" suggests that the specific catalog view dm_db_partition_stats might not be available or supported in your current version of SQL Server.

Instead, you might need to use an alternative method to gather information about storage usage. One alternative approach is to query the sys.allocation_units view to get similar information. Here's an example query:

SELECT 
    SUM(total_pages) * 8.0 / 1024 AS UsedStorageInGB
FROM 
    sys.allocation_units
WHERE 
    type_desc = 'IN_ROW_DATA';