SQL Server Database Size Report for All Databases

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

Which databases on this instance hold the most disk, and how large is each one in MB and GB? One query against sys.master_files and sys.databases answers that for every user database, sorted with the largest first.

Purpose and Overview

Capacity planning starts with a number per database: how much file space each one has allocated today, so that next month's number can be compared against it. sys.master_files is built for that. It is "a single, system-wide view" that "contains a row per file of a database as stored in the master database", so one connection sees the data files and log files of every database on the instance without switching context or looping through databases.

The script joins that view to sys.databases for the database name, adds up the size column per database, and converts the result from pages to megabytes and gigabytes. It also returns @@SERVERNAME, which makes the output easy to identify when results from several instances are collected into one table.

The number it reports is allocated file size, not used space. A 100 GB data file holding 30 GB of data counts as 100 GB here, because that is what the file occupies on disk. For most storage conversations that is the right figure, since the disk volume cares about the file and not the data inside it. When you need to know how full the files are, the Performance Considerations section points to the views that report used space.

Code Breakdown

The complete script is below, followed by a walk through each part.

 1USE master;
 2GO
 3SELECT
 4    @@SERVERNAME                                              AS server_name,
 5    d.name                                                    AS database_name,
 6    CAST(SUM(CAST(mf.size AS BIGINT)) * 8.0 / 1024
 7         AS DECIMAL(18, 2))                                   AS database_size_mb,
 8    CAST(SUM(CAST(mf.size AS BIGINT)) * 8.0 / 1024 / 1024
 9         AS DECIMAL(18, 2))                                   AS database_size_gb
10FROM sys.master_files AS mf
11INNER JOIN sys.databases AS d
12    ON d.database_id = mf.database_id
13WHERE d.name NOT IN (N'master', N'model', N'msdb', N'tempdb')
14GROUP BY d.name
15ORDER BY database_size_gb DESC;

sys.master_files

The script depends on four columns of sys.master_files. database_id is the database the file belongs to. type is the file type, where "0 = Rows" and "1 = Log" (FILESTREAM is 2). state_desc holds the file state, one of ONLINE, RESTORING, RECOVERING, RECOVERY_PENDING, SUSPECT, OFFLINE or DEFUNCT. And size is the "current file size, in 8-KB pages".

Because the view lives in master and covers every database, the query needs no cross-database references. Seeing a row still depends on permission: the minimum permissions needed to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE or VIEW ANY DEFINITION. A login without any of those gets a report that silently omits databases, so check the permissions of the account that runs the scheduled version.

Joining sys.databases

The INNER JOIN on database_id attaches the database name. sys.databases has its own visibility rules: unless the caller owns the database, seeing a row requires ALTER ANY DATABASE, VIEW ANY DATABASE or CREATE DATABASE in master, with the exception that "the database to which the caller is connected can always be viewed". By default the public role holds VIEW ANY DATABASE.

The WHERE clause excludes the four system databases by name. Filtering on names states the intent directly and does not depend on which ids the system databases hold. Remove the filter if you want system databases in the report; tempdb needs special handling, covered below.

Size calculation

Each size value is a count of 8 KB pages, so the conversion is:

1pages x 8      = kilobytes
2kilobytes / 1024 = megabytes
3megabytes / 1024 = gigabytes

The script casts size to BIGINT before summing, because size is an int column and a total over many large files is safest held in a wider type. The multiplication uses 8.0 so the arithmetic is decimal and a 5 MB database reports 5.00 rather than a truncated integer, and the final CAST to DECIMAL(18, 2) gives clean two-decimal output.

GROUP BY and ordering

A database normally has several rows in sys.master_files: at least one data file and one log file, and more with secondary data files. GROUP BY d.name collapses them into one total per database. ORDER BY database_size_gb DESC puts the biggest consumers at the top, which is where a capacity review starts.

Counting data and log files per database

The type column lets you split the total. This variant reports how many data files and log files each database has, and the size of each kind:

 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(CASE WHEN mf.type = 0 THEN CAST(mf.size AS BIGINT) ELSE 0 END)
 6         * 8.0 / 1024 AS DECIMAL(18, 2))                            AS data_size_mb,
 7    CAST(SUM(CASE WHEN mf.type = 1 THEN CAST(mf.size AS BIGINT) ELSE 0 END)
 8         * 8.0 / 1024 AS DECIMAL(18, 2))                            AS log_size_mb
 9FROM sys.master_files AS mf
10INNER JOIN sys.databases AS d
11    ON d.database_id = mf.database_id
12WHERE d.name NOT IN (N'master', N'model', N'msdb', N'tempdb')
13GROUP BY d.name
14ORDER BY data_size_mb DESC;

The split makes an unusually large log file visible in one pass; DBCC SQLPERF(LOGSPACE), covered below, then shows how much of it is in use.

Percentage of the instance

To see each database as a share of everything allocated on the instance, divide each database total by the grand total:

1SELECT
2    DB_NAME(database_id)                                            AS database_name,
3    CAST(SUM(CAST(size AS BIGINT)) * 8.0 / 1024 / 1024
4         AS DECIMAL(18, 2))                                         AS total_size_gb,
5    CAST(100.0 * SUM(CAST(size AS BIGINT))
6         / SUM(SUM(CAST(size AS BIGINT))) OVER () AS DECIMAL(5, 2)) AS pct_of_instance
7FROM sys.master_files
8GROUP BY database_id
9ORDER BY total_size_gb DESC;

The window aggregate SUM(SUM(...)) OVER () totals the per-database sums, so no second scan of the view is needed.

Key Benefits and Use Cases

  • One query for the whole instance — a single connection to master reports every database, with no per-database loop and no dynamic SQL.
  • Largest databases first — the sort order points capacity reviews at the databases that matter.
  • Data versus log split — the type column separates rows files from log files, which exposes oversized logs quickly.
  • A baseline for growth — run on a schedule and stored, the output becomes the month-over-month growth series that infrastructure teams ask for when they size storage.
  • Multi-instance reporting — @@SERVERNAME in the output lets results from many servers land in one table and still be told apart.
  • Pre-migration sizing — the figures give the volume of storage a database needs when planning a move to new hardware or a new volume.

Performance Considerations

  • Allocated, not used: size is the current file size. For used space within a data file, sys.dm_db_file_space_usage reports page counts such as total_page_count and allocated_extent_page_count for each data file, but it covers the current database only. sys.database_files is also per database and works with FILEPROPERTY(name, 'SpaceUsed').
  • Log usage: DBCC SQLPERF(LOGSPACE) returns the log size and the percentage of log space used for every database. From SQL Server 2012, sys.dm_db_log_space_usage is the DMV to use for per-database log usage instead.
  • Deferred deallocation: When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the page deallocations, so the values from sys.master_files immediately afterward "might not reflect the actual disk space available".
  • tempdb: "For the tempdb database, sys.master_files shows the initial tempdb size," and tempdb growth is not reflected in the view. The current size of the tempdb files comes from tempdb.sys.database_files. That is why this report leaves tempdb out rather than showing a number that can be stale.
  • FILESTREAM and snapshots: "This field is populated as zero for FILESTREAM containers," so FILESTREAM data is missing from the totals. sys.database_files reports the used size of those containers. For a database snapshot, size is the maximum space the snapshot can ever use, not its current usage.
  • Availability groups: For Always On databases, query database_state or database_state_desc in sys.dm_hadr_database_replica_states for state. The size query itself is unchanged, but confirm behavior on your version before relying on file paths from a secondary.

Practical Tips

  • Schedule it and store the result. A SQL Server Agent job that inserts the output, with a collection timestamp, into a history table gives you the growth trend. Add a collected_at column with SYSDATETIME() and keep one row per database per run.
  • Report tempdb separately. Read tempdb.sys.database_files for current tempdb file sizes, since sys.master_files shows only the initial size.
  • Check the account's permissions. The row-visibility rules for both views mean a low-privilege login produces a shorter list. Run the scheduled job under an account that holds VIEW ANY DEFINITION and ALTER ANY DATABASE, or compare its output once against a sysadmin run.
  • Pair it with log monitoring. Run DBCC SQLPERF(LOGSPACE) alongside the size report so a large log file is read together with how much of it is in use.
  • Watch the state. sys.master_files has a state_desc column. Add it to the SELECT and GROUP BY when you need to see files that are OFFLINE, RECOVERY_PENDING or SUSPECT, because a size total alone does not say whether the files are usable.
  • Compare against the volume. Sum the per-database totals and set them next to the free space on the volume that holds the files.

Conclusion

This report gives DBAs one query that lists the allocated size of every user database on an instance, split into MB and GB and sorted by size. It reads sys.master_files and sys.databases from master, so there is no looping or cross-database access. Remember what the number means: allocated file size, with tempdb, FILESTREAM and used space reported by other views. Scheduled into a history table, it becomes the growth record that capacity planning depends on.

References

Posts in this series