How to Get All Database Size in SQL Server Using Query?

by

In this SQL Server tutorial, I will show you how to get all database size in SQL Server using query.

In this tutorial, you will use the sp_helpdb stored procedure and sys.master_files view to retrieve the size of all the databases on the SQL Server.

Get All Database Size in SQL Server Using Query

To get all the database sizes in SQL Server using a query, there are different ways. Getting the size of the database in SQL Server is helpful for several reasons.

If you know the size of the database, you can manage the resources in an effective manner that allows you to monitor and allocate the resources efficiently.

For example, if your database is growing very quickly, and you know the size of the database, you can plan for additional storage in advance to stop running out of disk space.

For better performance of the databases, different optimization strategies are implemented. So, if you know the size of the database, then you can implement the best optimization strategies for performance tuning of the database.

If you track the database size as time passes, you can perform the data growth analysis. The data growth analysis helps in budgeting and capacity planning.

Now, let’s know how to know the size of all the databases on SQL Server instances using the different methods.

Get All Database Size in SQL Server Using Query: sp_helpdb

The sp_helpdb is the stored procedure on SQL Server, which gets the information related to the databases. It returns information such as database name, size, owner, compatibility level, collation, creation date, status, recovery model, etc.

So open the command prompt or the query editor on SQL Server Management Studio and run the below query to get all database sizes.

EXEC sp_helpdb;
GO
sp_helpdb Get All Database Size in SQL Server Using Query sp_helpdb

In the result set, look at the column db_size, which contains the size of each database, such as E_commerce, master, model, msdb, Products, and tempdb. These are all the databases on the current SQL Server instance.

For example, the size of the E_commerce is 16.00 MB, the master is 5.25 MB, the model is 16.00 MB, and so on.

This is how to get all database size in SQL Server using query with the help of sp_helpdb stored procedure.

Get All Database Size in SQL Server Using Query: sys.master_files

You can also check the size of all the databases on the SQL Server instance using the sys.master_files system view. This is the built-in view in SQL Server, which keeps information related to all the databases. But it also contains log file information.

So, use the below query to know the size of all the databases on the SQL Server instance.

SELECT
DB_NAME(database_id) AS 'database name',
SUM(size * 8 / 1024) AS 'size (MB)'
FROM sys.master_files
GROUP BY DB_NAME(database_id);
sys.master_files Get All Database Size in SQL Server Using Query

Look at the above output that shows the databases with their size in the size (MB) column. For example, msdb size is 39, Products is 16, and same for the all databases.

  • Let’s understand the query; the query part DB_NAME(database_id) returns the name of the database on the provided database ID. Here DB_NAME() is the function, and the database_id is the column in the sys.master_files view that contains each database ID.
  • Then, the query part SUM(size * 8 / 1024) AS ‘size (MB) computes the size of each database in MB (megabytes). There is a column named size in the sys.master_files, which contains the size of the database file in 8KB pages.
  • After the query converts the 8KB pages into MB by multiplying the 8/1024, the SUM() is an aggregate function that combines the sizes of all the files that are related to each database.
  • Then, this query part GROUP BY DB_NAME(database_id) groups the results sets based on the name of the database.

This is how to find the size of all the databases on the SQL Server using the sys.master_files view. If you want to know how to get the size of a database, then refer to this tutorial Check Database Size in SQL Server Using Query.

Conclusion

In this SQL Server tutorial, you learned how to get the size of all the databases on SQL Server using the query. Here, you learned about two methods: one uses the sp_helpdb procedure, and the second uses sys.master_files to view the database’s size.

You may like the following tutorials: