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.

Get SQL Database Size

The size of a database in SQL Server may be measured in exclusive ways, depending on what we need to include or exclude· For instance, we can measure the scale of the facts documents, the log documents, the reserved space, the used area, the unallocated area, or the whole space· We also can measure the size of a single database, a set of databases, or all databases within the server.

Some methods to get MySQL server database size are:

Using the GUI Tools

One of the easiest ways to get the size of a database in SQL Server is to use GUI tools, such as SQL Server Management Studio (SSMS) or Azure Data Studio· These tools provide various reports and properties that display the size of a database in a user-friendly way·

For example, in SSMS:

• right-click on a database
• Go to Reports, then Standard Reports, and then click Disk Usage·

This will open a report that shows the disk space used by the data and log files of the database, as well as the unallocated space and the reserved space· The report also shows a pie chart and a bar chart that visualizes the system-stored distribution of the space·

GUI

Output:

Output

Advantages and Disadvantages of using the GUI tools

Advantages	Disadvantages
Easy to use	Not suitable for automation or scripting
Povide a clear and comprehensive view of the size of a database	May not be available or accessible in some environments

Using the sp_spaceused Stored Procedure

Another way to get the size of a database in SQL Server is to use the sp_spaceused system stored procedure· This stored procedure 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, we simply switch to the relevant database and execute the procedure.

USE tempdb;
EXEC sp_spaceused;

Output:

sp_helpdb Stored Procedure

sp_spaceused Stored Procedure:

sp_spaceused Stored Procedure

Explanation: This will return two result sets that provide the relevant information. The first result set shows the name, the size, and the unallocated space of the database. The second result set shows the reserved space, the data space, the index space, and the unused space of the database.

Using the sp_helpdb Stored Procedure

Another system-stored procedure that can be used to get the size of a database in SQL Server is sp_helpdb· This stored procedure lists databases that either reside in an instance of the SQL Server or are accessible through a database gateway·

To use it, we can either provide the name of the database as an argument or execute it without any argument.

EXEC sp_helpdb N'tempdb';

Using the sp_databases Stored Procedure

Yet another option to get the size of a database in SQL Server 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·

To use it, we simply execute it without any argument.

EXEC sp_databases;

Output:

sp_databases Stored Procedure

Explanation: This will return one result set that shows the name, the size, and the remarks of each database.

Advantages and Disadvantages of using sp_databases Stored Procedure

Advantages	Disadvantages
It is compatible with earlier versions of SQL Server and other database systems.	Only shows the total size of the database, not the breakdown of the data and log files or the reserved and used space. It also does not provide any additional information about the database.

Using the sys.master_files Catalog View

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:

sys.master_files Catalog View

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.

Using the sys.databases Catalog View

The last method we will cover in this article is to use the sys·databases catalog view· This view contains one row per database in the instance of SQL Server·

To use it, we can write a query that joins the sys·databases view with the sys·master_files view to get the size of the database and its files·

sys.databases Catalog View

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.databases Catalog View

Advantages	Disadvantages
It provides additional information about the database, such as the recovery model, the collation, the state, and the compatibility level.	1. Requires writing a more complex query and joining two views.
it is the most general interface to the catalog metadata, providing the most efficient way to obtain, transform, and present customized forms of this information.	It does not contain information about replication, backup, database maintenance plan, or SQL Server Agent catalog data.
It contains one row per database in the instance of SQL Server, making it easy to get an overview of all databases.	If a database isn’t ONLINE, or AUTO_CLOSE is set to ON and the database is closed, the values of some columns can be NULL

Conclusion

This article explained various methods to determine a database size in SQL Server, including GUI tools, system stored procedures, and system catalog views·

Each method’s pros and cons are discussed, with examples provided· The article emphasizes that measurement results can vary based on what’s included/excluded and the data’s accuracy and currency