Modern Approach to Query All Database Sizes in SQL Server 2008

Introduction

In SQL Server database administration practice, monitoring database file sizes represents a crucial daily maintenance task. Particularly in environments with numerous database instances, such as the scenario described with 500 databases, efficiently and accurately obtaining data file and log file size information for each database holds significant importance for capacity planning, performance optimization, and storage management.

Core Technical Principles

SQL Server provides comprehensive system views for accessing database metadata information. Among these, the sys.master_files view contains file-level information for all databases, while the sys.databases view offers database-level metadata. Understanding the structure and relationships of these two system views forms the foundation for constructing efficient queries.

The type field in the sys.master_files view distinguishes file types: value 0 indicates data files (ROWS), and value 1 indicates log files (LOG). The size field stores file size in 8KB page units, which is SQL Server's internal standard storage unit.

Query Implementation Details

Based on these principles, we can construct an efficient Common Table Expression (CTE) query to retrieve size information for all databases:

WITH file_sizes AS (
    SELECT 
        database_id, 
        type, 
        size * 8.0 / 1024 AS size_mb
    FROM sys.master_files
)
SELECT 
    db.name AS DatabaseName,
    COALESCE((SELECT SUM(size_mb) FROM file_sizes WHERE type = 0 AND file_sizes.database_id = db.database_id), 0) AS DataFileSizeMB,
    COALESCE((SELECT SUM(size_mb) FROM file_sizes WHERE type = 1 AND file_sizes.database_id = db.database_id), 0) AS LogFileSizeMB
FROM sys.databases db
ORDER BY db.name;

The implementation logic of this query can be divided into several key steps: First, file size information is preprocessed through CTE, converting the original 8KB page units to MB units for easier understanding and comparison. In the conversion formula size * 8.0 / 1024, multiplying by 8 converts page count to KB (since each page is 8KB), then dividing by 1024 converts to MB.

In the main query section, correlated subqueries calculate data file size and log file size for each database respectively. The COALESCE function ensures that even if some databases lack files of specific types, the query returns 0 instead of NULL, enhancing result usability.

Technical Detail Analysis

In the backup management scenarios discussed in the reference article, database size information similarly holds significant value. Although backup sizes differ from actual database sizes (due to compression, incremental backups, etc.), database size remains the fundamental metric for evaluating backup strategies and storage requirements.

Permission management requires special attention in practical applications. As mentioned in the reference article, accessing the sys.master_files view requires appropriate permissions. In environments with restricted permissions, queries may fail to return expected results. It is recommended to ensure the executing user has at least VIEW SERVER STATE permission.

For large-scale environments (such as 500 databases), query performance optimization becomes particularly important. This solution pre-calculates file sizes through CTE, avoiding repeated unit conversions in subqueries and improving execution efficiency. Meanwhile, using correlated subqueries instead of JOIN operations reduces unnecessary Cartesian products.

Application Scenario Extensions

In actual management work, various extensions can be built upon this basic query: adding database status information (state_desc), recovery mode (recovery_model_desc), and other fields to construct more comprehensive database monitoring reports. It can also integrate with backup information (as discussed in the reference article's msdb.dbo.backupset) to achieve complete database health assessment.

For specific requirements, filtering conditions can be added to exclude system databases:

WHERE db.name NOT IN ('master', 'model', 'msdb', 'tempdb')

Such filtering proves particularly useful when generating user database reports, avoiding the impact of system databases on statistical analysis.

Conclusion

The query solution provided in this paper fully utilizes SQL Server's system view characteristics to achieve efficient and accurate database size monitoring. Through in-depth understanding of core technologies including file type identification and size unit conversion, combined with practical considerations for permission management and performance, it offers database administrators a reliable solution. This approach is not only applicable to SQL Server 2008 R2 but remains effective in newer SQL Server versions, demonstrating good compatibility and extensibility.