SQL Server Database Size Report for All Databases

calendar Apr 2, 2026 / · 6 min read · sql server monitoring sql scripts sys.master_files sys.databases database administration capacity planning storage reporting maintenance scripts  ·
Share on: linkedin copy

Report Database Size in MB and GB for All SQL Server Databases

This script queries sys.master_files and sys.databases to report the total allocated size in megabytes and gigabytes for every user database on the SQL Server instance, sorted by largest database first.

Purpose and Overview

Knowing how large each database is — and tracking how that size changes over time — is a core part of SQL Server capacity planning. Running out of disk space is one of the most disruptive events a DBA can face: it halts log backups, prevents transactions from committing, and can take databases offline. This script, adapted from the DBA-Scripts collection by Bulent Gucuk, reads allocated file sizes from sys.master_files and joins to sys.databases to produce a single server-wide size report. It runs from the master database, requires no cross-database queries, and takes milliseconds to execute — making it safe to run at any time, including during a production incident.

Code Breakdown

The complete script is shown below, followed by an explanation of each section.

 1USE master;
 2GO
 3SELECT
 4    @@SERVERNAME                                                            AS server_name,
 5    d.name                                                                  AS database_name,
 6    CAST(SUM(mf.size) AS BIGINT) * 8 / 1024                                AS database_size_mb,
 7    CAST(
 8        CAST((CAST(SUM(mf.size) AS BIGINT) * 8 / 1024) AS NUMERIC(10, 2))
 9        / 1024 AS NUMERIC(10, 2)
10    )                                                                       AS database_size_gb
11FROM sys.master_files AS mf
12INNER JOIN sys.databases AS d
13    ON d.database_id = mf.database_id
14WHERE d.database_id > 4   -- exclude system databases
15GROUP BY d.name
16ORDER BY database_size_gb DESC;

sys.master_files

sys.master_files is a catalog view in the master database that contains one row for every data and log file across all databases on the instance — including offline and detached databases that are still registered. The size column stores the current allocated file size in 8 KB pages. Multiplying by 8 converts pages to kilobytes; dividing by 1,024 converts kilobytes to megabytes.

Because sys.master_files is in master, this query works without switching database context or using cross-database references. All file sizes are visible from a single place.

Joining sys.databases

The INNER JOIN to sys.databases on database_id links each file row to its parent database and provides the database name. The filter WHERE d.database_id > 4 excludes the four system databases: master (1), tempdb (2), model (3), and msdb (4). Remove this filter if you want system databases included in the report.

Size Calculation

sys.master_files.size is in 8 KB pages. The conversion chain is:

1pages × 8 = kilobytes
2kilobytes / 1,024 = megabytes
3megabytes / 1,024 = gigabytes

The CAST(SUM(mf.size) AS BIGINT) prevents integer overflow on servers with very large databases where the page count exceeds the range of a standard INT. The outer CAST(... AS NUMERIC(10, 2)) produces a clean two-decimal-place gigabyte figure.

GROUP BY and Ordering

GROUP BY d.name aggregates all data and log files for each database into a single total. A database with multiple data files (such as a filegroup with several NDF files) and a log file (LDF) will have multiple rows in sys.master_files — the GROUP BY sums them all. ORDER BY database_size_gb DESC puts the largest databases at the top, which is where attention should focus first during capacity planning.

Key Benefits and Use Cases

  • Instant server-wide storage snapshot with no cross-database queries
  • Works from a single connection to master — no looping through databases required
  • Sorts largest databases first for immediate capacity focus
  • Safe to run at any time including production incidents — reads only catalog metadata
  • Serves as a baseline for tracking database growth trends when run regularly
  • @@SERVERNAME in the output makes results identifiable when aggregating across multiple instances

Performance Considerations

  • Allocated vs. used space: sys.master_files.size reflects the space currently allocated to the file on disk — not the space actually used by data. A 100 GB data file with 30 GB of actual data will still report as 100 GB. To report used space, query sys.dm_db_file_space_usage per database or use DBCC SQLPERF('LOGSPACE') for log files.
  • Auto-growth events: The allocated size grows with auto-growth events and shrinks only when files are explicitly shrunk. A database that had a large import and then deleted the data will still show the full pre-import allocated size.
  • tempdb: tempdb is excluded by the database_id > 4 filter. If you need to monitor tempdb size separately, query sys.master_files WHERE database_id = 2 directly, or include it by removing the filter.
  • Availability Groups: On an Availability Group secondary, sys.master_files shows the secondary replica's file sizes, which may differ slightly from the primary if auto-growth has occurred on one but not the other.

Practical Tips

To also show the number of data files and log files per database, extend the GROUP BY with a CASE expression:

 1SELECT
 2    d.name                                              AS database_name,
 3    SUM(CASE WHEN mf.type = 0 THEN 1 ELSE 0 END)       AS data_file_count,
 4    SUM(CASE WHEN mf.type = 1 THEN 1 ELSE 0 END)       AS log_file_count,
 5    CAST(SUM(mf.size) AS BIGINT) * 8 / 1024            AS total_size_mb,
 6    CAST(CAST((CAST(SUM(mf.size) AS BIGINT) * 8 / 1024)
 7         AS NUMERIC(10, 2)) / 1024 AS NUMERIC(10, 2))  AS total_size_gb
 8FROM sys.master_files AS mf
 9INNER JOIN sys.databases AS d ON d.database_id = mf.database_id
10WHERE d.database_id > 4
11GROUP BY d.name
12ORDER BY total_size_gb DESC;

mf.type = 0 identifies data files (MDF and NDF); mf.type = 1 identifies log files (LDF).

For a visual size comparison using proportional bars — useful in a quick console report — this alternative from the sqlserver-kit community collection shows each database as a percentage of total instance storage:

 1SELECT
 2    DB_NAME(database_id)                            AS database_name,
 3    CAST(SUM(size) * 8.0 / 1024 / 1024 AS DECIMAL(30, 2))  AS total_size_gb,
 4    CAST(
 5        (SUM(size) / (SELECT SUM(CONVERT(DECIMAL(30, 4), size))
 6                      FROM sys.master_files WITH (NOWAIT))) * 100.00
 7    AS DECIMAL(10, 2))                              AS pct_of_instance,
 8    REPLICATE('|',
 9        CONVERT(INT,
10            (SUM(size) / (SELECT SUM(CONVERT(DECIMAL(30, 2), size))
11                          FROM sys.master_files WITH (NOWAIT))) * 100
12        )
13    )                                               AS size_graph
14FROM sys.master_files WITH (NOWAIT)
15GROUP BY database_id
16ORDER BY total_size_gb DESC;

Schedule the main script as a daily SQL Server Agent job that inserts results into a history table. Querying that table over time shows monthly growth rates per database, which feeds directly into capacity planning conversations with infrastructure teams.

Conclusion

This script gives DBAs a fast, reliable view of database storage allocation across the entire SQL Server instance. Reading directly from sys.master_files in master means no looping, no dynamic SQL, and no cross-database permission issues. The MB and GB columns make the output immediately useful for capacity planning, and sorting by size descending puts the most important databases first. Add the data file and log file counts, schedule it as a daily job, and store historical results to build a storage growth trend report that requires no third-party tooling.

References

Posts in this series