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
锘縏he 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
- Using the sp_spaceused Stored Procedure
- Using the sp_helpdb Stored Procedure
- Using the sp_databases Stored Procedure
- Using the sys.master_files Catalog View
- Using the sys.databases Catalog View
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路
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_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:
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. |
|
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:
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路
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鈥檛 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鈥檚 pros and cons are discussed, with examples provided路 The article emphasizes that measurement results can vary based on what鈥檚 included/excluded and the data鈥檚 accuracy and currency