Menu
How can I calculate the current size of a SQL Azure Database? (Not the maximum size limit)
Several places (like this) suggest using this sql:
SELECT SUM(reserved_page_count) * 8192
FROM sys.dm_db_partition_stats
However when executing this as the Administrator login I get this error:
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.
857
database_files DMV is the more accurate way to know the size of the database. There are multiple ways to calculating the size of the database as exposed on the article. Users have also documented this discrepancy between Portal and T-SQL on the Web.
Azure SQL database has a different size for different plans, like for Basic plan, it offers 2Gb, for Standard tier it offers 250Gb, and for the Premium tier it offers a storage limit up to 1Tb.
To estimate the size of a database, estimate the size of each table individually and then add the values obtained. The size of a table depends on whether the table has indexes and, if they do, what type of indexes.
The size of the database is the space the files physically consume on disk. You can find this with: select sum(bytes)/1024/1024 size_in_mb from dba_data_files; But not all this space is necessarily allocated.
How big is my Azure SQL database? Mar 13 2019 06:14 PM Assuming you have S3 database you will be limited to maximum of 250GB data size. This is not because we happy to give free bytes, this is because of the way we calculate the size. So 268,435,456,000 / 1000 / 1000 / 1000 = 268.435456 which is exactly the size shown on the portal.
If you're using SQL Server Management Studio, choose your database first (not the master database), and execute the query. This should work. Oh and by the way, if you want the size in megabytes, try the following: OR You can check your database size and its usage through Windows Azure Management Portal also.
This is probably because you're running the query on the master database. If you're using SQL Server Management Studio, choose your database first (not the master database), and execute the query. This should work. Oh and by the way, if you want the size in megabytes, try the following:
For V12 databases, the measurement we are interested in is determined using the sys.database_files DMV and the FILEPROPERTY function with the 'SpaceUsed' argument. Only ROWS files are considered. Log and XTP files are excluded for the purposes of determining database size. SELECT SUM (CAST (FILEPROPERTY (name, 'SpaceUsed') AS bigint) * 8192.)
This is probably because you're running the query on the master database. If you're using SQL Server Management Studio, choose your database first (not the master database), and execute the query. This should work. Oh and by the way, if you want the size in megabytes, try the following:
SELECT (SUM(reserved_page_count) * 8192) / 1024 / 1024 AS DbSizeInMB
FROM sys.dm_db_partition_stats
OR
You can check your database size and its usage through Windows Azure Management Portal also.
Source: https://azure.microsoft.com/en-us/documentation/articles/sql-database-monitoring-with-dmvs/
82
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
