DP-300: Administering Microsoft Azure SQL Solutions

Question

You have an Azure SQL database named DB1. You need to determine how much unused space, in megabytes, has been allocated to DB1. How should you complete the Transact-SQL query?

Select

SELECT SUM(size/ - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)
AS DatabaseDataSpaceAllocatedUnusedInMB
FROM 
GROUP BY type_desc
HAVING type_desc = 'ROWS';

Explanation

The size column in sys.database_files is expressed in 8KB pages, so dividing by 128.0 converts the value to megabytes (128 pages x 8KB = 1024KB = 1MB). The query subtracts the space used (also converted from pages to MB via FILEPROPERTY(name,'SpaceUsed')/128.0) from the total allocated space to derive the unused allocated space. sys.database_files is the catalog view that exposes the size and name of each database file needed for this calculation; sys.resource_stats and sys.dm_db_resource_stats instead report CPU/IO/memory resource consumption over time and do not contain file size information, so they cannot be used for this query.

Learn more

DP-300: Administering Microsoft Azure SQL Solutions

AstroTutor

Notes

Readiness

QuestionQ24

Community Discussion