6 Ways to Check the Size of a Database in SQL Server using T-SQL

If you’re using a GUI tool, such as SSMS to manage your databases, you can easily check the size of your database by clicking your way through the GUI (right-click the database, point to Reports, then Standard Reports, and then click Disk Usage).

However, if you prefer to use T-SQL to manage your databases, you’ll need to run a query that returns this information.

This article presents six ways to check the size of a SQL Server database using T-SQL.

The sp_spaceused Stored Procedure

This is a system stored procedure that displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

To use it, simply switch to the relevant database and execute the procedure. Like this:

USE WideWorldImporters;
EXEC sp_spaceused;

Result:

database_name       database_size  unallocated space
------------------  -------------  -----------------
WideWorldImporters  3172.00 MB     2511.76 MB       

1 row(s) returned

reserved   data       index_size  unused 
---------  ---------  ----------  -------
573688 KB  461728 KB  104120 KB   7840 KB

1 row(s) returned

This returns two result sets that provide the relevant information.

You can also provide an object name to return data on a specific object within the database. In this case, only one result set will be returned.

Example:

USE WideWorldImporters;
EXEC sp_spaceused N'Application.Cities';

Result:

name    rows                  reserved  data     index_size  unused
------  --------------------  --------  -------  ----------  ------
Cities  37940                 4880 KB   3960 KB  896 KB      24 KB

In this example we return information about the Cities table only.

The sp_helpdb Stored Procedure

Another system stored procedure is sp_helpdb.

Here’s an example of calling that:

EXEC sp_helpdb N'WideWorldImporters';

Result:

name          fileid  filename          filegroup  size        maxsize        growth    usage    
------------  ------  ----------------  ---------  ----------  -------------  --------  ---------
WWI_Primary   1       /data/WWI.mdf     PRIMARY    1048576 KB  Unlimited      65536 KB  data only
WWI_Log       2       /data/WWI.ldf     null       102400 KB   2147483648 KB  65536 KB  log only 
WWI_UserData  3       /data/WWI_UD.ndf  USERDATA   2097152 KB  Unlimited      65536 KB  data only

In this case, we pass the name of the database as an argument. We can also call sp_helpdb without providing an argument. If we do this, it will return information on all databases in the sys.databases catalog view.

The sp_databases Stored Procedure

Yet another option is the sp_databases system stored procedure. This stored procedure lists databases that either reside in an instance of the SQL Server or are accessible through a database gateway.

Here’s how to execute it:

EXEC sp_databases;

Result:

DATABASE_NAME       DATABASE_SIZE  REMARKS
------------------  -------------  -------
master              6848           null   
model               16384          null   
msdb                15616          null   
Music               16384          null   
Nature              16384          null   
Solutions           47104          null   
tempdb              16384          null   
Test                16384          null   
WideWorldImporters  3248128        null   
world               16384          null   
WorldData           16384          null

The sys.master_files View

The above stored procedure queries the sys.master_files view. So an alternative is to go straight to the view and cherry pick your columns:

SELECT
    name,
    size,
    size * 8/1024 'Size (MB)',
    max_size
FROM sys.master_files
WHERE DB_NAME(database_id) = 'WideWorldImporters';

Result:

name          size    Size (MB)  max_size 
------------  ------  ---------  ---------
WWI_Primary   131072  1024       -1       
WWI_Log       12800   100        268435456
WWI_UserData  262144  2048       -1       

In this case we can see the size of each data file and log file, as they’re listed separately. You’ll also notice that I perform a calculation on the size column to convert the value into megabytes (MB).

The sys.database_files View

There’s also a system view called sys.database_files. We can use this view to return the same info as the previous example:

USE WideWorldImporters;
SELECT
    name,
    size,
    size * 8/1024 'Size (MB)',
    max_size
FROM sys.database_files;

Result:

name          size    Size (MB)  max_size 
------------  ------  ---------  ---------
WWI_Primary   131072  1024       -1       
WWI_Log       12800   100        268435456
WWI_UserData  262144  2048       -1       

Use a Window Function

One potential issue with the previous two examples is that they list out the size of each file separately. This could be seen as a positive or a negative depending on what you want to achieve.

It could also be argued that the first three solutions on this page are problematic, because they only provide the sum total of all files – they don’t list out each individual file along with its size.

So what if you want to see both the size of each individual file, and the total of all files for each database?

You could use the OVER clause to do exactly that.

Here’s an example:

SELECT
    d.name AS 'Database',
    m.name AS 'File',
    m.size,
    m.size * 8/1024 'Size (MB)',
    SUM(m.size * 8/1024) OVER (PARTITION BY d.name) AS 'Database Total',
    m.max_size
FROM sys.master_files m
INNER JOIN sys.databases d ON
d.database_id = m.database_id;

Result:

Database            File             Size (MB)  Database Total
------------------  ---------------  ---------  --------------
master              master           4          6             
master              mastlog          2          6             
model               modeldev         8          16            
model               modellog         8          16            
msdb                MSDBData         14         14            
msdb                MSDBLog          0          14            
Music               Music            8          16            
Music               Music_log        8          16            
Nature              Nature           8          16            
Nature              Nature_log       8          16            
Solutions           Solutions        8          46            
Solutions           Solutions_log    8          46            
Solutions           Solutions_dat_2  10         46            
Solutions           Solutions_dat_3  10         46            
Solutions           Solutions_log_2  10         46            
tempdb              tempdev          8          16            
tempdb              templog          8          16            
WideWorldImporters  WWI_Primary      1024       3172          
WideWorldImporters  WWI_Log          100        3172          
WideWorldImporters  WWI_UserData     2048       3172          
world               world            8          16            
world               world_log        8          16       

This lists out each database, the files for each database, the file size for each file, as well as the total of all files for each database. This requires that each database (and their total size) is listed multiple times (once for each file).