How to Check Database Size in SQL Server Using Query?

by

In this SQL Server tutorial, you will learn how to check database size in SQL Server using query.

You will understand the stored procedure in SQL Server that contains information about the databases and how to check the size of databases.

Also, you will understand about view files in SQL Server to retrieve the database information such as database, size, etc.

Check Database Size in SQL Server Using Query

In SQL Server, there are multiple ways to check database size in SQL Server using query. From an administrative and monitoring point of view in SQL Server, checking the size of the database is very important.

So checking the size of the database is crucial for capacity planning, backup and restore, performance optimization, cost management, and resource allocation.

Let’s know about different ways to check the database size in SQL Server.

Check Database Size in SQL Server Using Query: sp_spaceused

First, open connect to SQL Server instance using SQLCMD utility, so execute the below command in your terminal.

SQLCMD -S MSI\SQLEXPRESS

Now, the sp_spaceused is a stored procedure in SQL Server that exists in the master database, and this stored procedure stores information about spaces used by the database, table, data, indexes, etc.

To check the database size, the syntax is given below.

USE database_name;
EXEC sp_spaceused;
GO

Where,

  • USE database_name: Use this command to connect to a specific database whose size you want to find.
  • EXEC sp_spaceused: It executes the sp_spaceused procedure to show the space usage by the current database.

Now in your SQL command prompt, type the below query.

USE master;
EXEC sp_spaceused;
GO
Check Database Size in SQL Server Using Query sp_spaceused

When executing the above query, first it connects with the master database using the ‘USE master’ command. After that calls to execute the sp_spaceused procedure to retrieve the space usage by the master database.

In the output, you can see the size of the master database is 5.25 Mb. This is how to check the database size in SQL Server using the sp_spaceused procedure.

Check Database Size in SQL Server Using Query: using sp_helpdb

SQL server also has another store procedure sp_helpdb, when you execute or call this procedure it retrieves the result set that shows the information related to each database.

The sp_helpdb returns the information such as name, dbid, created, status, db_size, owner, and compatibility of each database.

Whether you are using the command line or query editor in SSMS, run this query.

EXEC sp_helpdb;
GO
Check Database Size in SQL Server Using Query sp_helpdb

The above query returns the name of each database with their size that you can see in the above output, look at the column name and db_size. For example, the size of the master and model databases is 5.25 and 16.00 MB respectively.

Also, it shows information such as database ID in dbid column, database creation date in the created column, owner of the database in the owner column, and compatibility_level.

This is how you can find the size of each database in SQL Server using the sp_helpdb system procedure.

Check Database Size in SQL Server Using Query: using sys.master_files view

The sys.master_files file is a system view in SQL Server, and it stores information about all the databases that exist on the SQL Server instance. It contains information in the data and log files of the database.

To check the size of the current database, use the below query.

SELECT
    DB_NAME(database_id) AS 'database mame',
    SUM(size * 8 / 1024) AS 'size (MB)'
FROM sys.master_files
WHERE database_id = DB_ID()
GROUP BY DB_NAME(database_id);
Check Database Size in SQL Server Using Query sys.master_files View

When you execute the above query it returns the size of the current database to which you are connected. So from the above output master database is a current database and its size is 5 MB.

If you want to check the size of all the databases on the SQL Server instance, use the below query.

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);
Check All Database Size in SQL Server Using Query sys.master_files View

The above query returns the size of all the databases on the SQL Server instance that you can see in the above picture. The column database name contains the name of the database and the size (MB) shows the size of each database.

This is how to check database size in SQL Server using system_master_files view.

Check Database Size in SQL Server Using Query: using sys.database_files view

In the previous section, you used sys.master_files view to check the size of the databases. SQL Server also provides another view called sys.database_files.

The sys.database_files view returns the size of the currently connected database on the SQL Server instance. It returns the information related to the database in two files, the first is the data and the second is the log file.

Now use the below queries to check the size of the currently connected database. So first connect to the specific database using the below query.

USE model;

After the execution of the above query, you get connected to the model database in the SQL Server instance.

Use the below query to check the size of the currently connected database which is the model in this case.

SELECT
    name AS 'database name',
    size * 8 / 1024 AS 'size (MB)'
FROM sys.database_files;
Check Database Size in SQL Server Using Query using sys.database_files view

Look at the above picture, the query returns to the two database names modeldev, and modellog of size 8 MB of the currently connected database.

In SQL Server, the two files are always associated with the database the data and log files.

  • The data files contain information about the actual data of the database such as indexes, objects, tables etc. So in the above modeldev is data file.
  • The log files contain information about whatever changes are made to the database. So modellog is the log file.

This is how you can use the sys.database_files to check database size in SQL Server using query.

Conclusion

In this SQL Server tutorial, you learned how to check database size in SQL Server using query. While checking database size, you learned about two stored procedures sp_spaceused and sp_helpdb, after that also learned about sys.master_files and sys.databse_files to check the size of the databases in SQL Server.

You may like: