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).
Ref: https://database.guide/6-ways-to-check-the-size-of-a-database-in-sql-server-using-t-sql/