How to use the sys.master_files Catalog View In SQL
Another way to get the size of a database in SQL Server is to use the sys·master_files catalog view· This view contains a row per file of a database as stored in the master database·
To use it, we can write a query that aggregates the size of the files by the database ID or the database name·
SELECT database_name = DB_NAME (database_id) , log_size_mb = CAST (SUM (CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL (8,2)) , row_size_mb = CAST (SUM (CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL (8,2)) , total_size_mb = CAST (SUM (size) * 8. / 1024 AS DECIMAL (8,2)) FROM sys.master_files GROUP BY database_id;
Output:
Explanation: This will return one result set that shows the name, the log size, the row size, and the total size of each database in megabytes.
Advantages and Disadvantages of using the sys.master_files catalog view
Advantages |
Disadvantage |
---|---|
It provides the most accurate and up-to-date information about the size of the database and its files. |
Requires writing a more complex query and may not be compatible with earlier versions of SQL Server. |
How to Get Database Size in SQL
SQL database size is vital for effective management. It indicates the storage space occupied by tables, indexes, and other components. Knowing the size of a database is useful for various purposes, such as monitoring the growth, estimating the backup time, planning the storage capacity, and optimizing performance·
In this article, we will learn how to get the size of a database in SQL Server using different queries or management tools.