Dedicated SQL pool storage capacity and database details

Amit Kamble 40

Hello,

As per our client they have provisioned Dedicated SQL pool for 5 Years with 5 TB storage capacity, and I need to find how much storage been occupied and how much storage is left. anyone can assist me on this please?

Deepanshukatara-6769 10,455 Reputation points

2023-12-05T05:11:21.9666667+00:00
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
Amit Kamble 40 Reputation points

2023-12-05T09:20:39.7633333+00:00

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
Deepanshukatara-6769 10,455 Reputation points

2023-12-05T10:21:59.07+00:00
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';
Amit Kamble 40 Reputation points

2023-12-05T11:14:59.4666667+00:00
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

After running

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

it is giving below error
Catalog view 'allocation_units' is not supported in this version.

1 answer

Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2023-12-11T20:51:48.1266667+00:00
Hello Amit Kamble,

Are you talking about TempDB size?

if yes, Microsoft Provides 399 GB per DW100c. For example, at DWU1000c, tempdb is sized to 3.99 TB.

You can run the below query against the 'master' to check the tempDB space used/ free

-- Determining the Amount of Space Used / free SELECT [Source] = 'database_files' ,[TEMPDB_max_size_MB] = SUM(max_size) * 8 / 1024.0 ,[TEMPDB_current_size_MB] = SUM(size) * 8 / 1024.0 ,[FileCount] = COUNT(FILE_ID) FROM tempdb.sys.database_files WHERE type = 0 --ROWS

If you are talking about user databases, Here is the Max size limits on the user databases.

Gen1: 240 TB compressed on disk. This space is independent of tempdb or log space, and therefore this space is dedicated to permanent tables. Clustered columnstore compression is estimated at 5X. This compression allows the database to grow to approximately 1 PB when all tables are clustered columnstore (the default table type).

Gen2: Unlimited storage for columnstore tables. Rowstore portion of the database is still limited to 240 TB compressed on disk.

So techincally, you will get unlimited storage with the synapse dedicated SQL pool DB's.

Please check the below document for your reference:
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-service-capacity-limits#database-objects

Let me know if you have any further questions.
Please sign in to rate this answer.

1 person found this answer helpful.
Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2023-12-16T00:01:49.4333333+00:00

Hello Amit Kamble,

I am checking to see if you have any further questions here.

If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.

Massimiliano Figini 0 Reputation points

2024-06-23T08:36:44.91+00:00

EDIT: I commented before reading carefully the answer but incredibly I cannot delete my own comment after few seconds I wrote it, but only edit it....
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Dedicated SQL pool storage capacity and database details

1 answer

Your answer