How to Check Database Size in SQL Server [In-depth Guide]

You can rapidly determine the SQL Server database size in SQL Server Management Studio (SSMS) if you only need to examine one database: Click Reports -> Standard Reports -> Disk Usage after doing a right-click on the database. 

To obtain database size instead, use a stored procedure like exec sp_spaceused. Both of these approaches, meanwhile, only display the size of a single database. You must utilize T-SQL queries, which are intricate and demand more advanced scripting skills, to process all databases on SQL Server. The details are covered in the following sections.

Ways to Determine SQL Server Database Size

A database’s size needs to be estimated frequently by SQL database managers. For instance, a database may need to be normalized if it is too large for the organization; alternatively, if the size is smaller than anticipated, you may be able to denormalize the database to enhance query efficiency. 

It is typical to encounter file size restrictions because large databases typically require powerful technology, or at the very least, a lot of space. You can use a variety of techniques and queries to determine a database’s size in SQL Server. Here are some various methods for achieving this.

1. Using SSMS (SQL Server Management Studio)

SSMS (SQL Server Management Studio) can be used to view the size of databases because it has a graphical user interface. Open SQL Server Management Studio first for this. Connect to the SQL Server instance you want next. 

Open the “Databases” node in the Object Explorer. To check the database’s size, right-click it and choose “Properties.” You may discover details on the database size, as well as the sizes of the data and log files, in the “General” section.

2. Using sp_spaceused System Stored Procedure

By using the sp_spaceused system stored procedure, you can learn more about how much space a database is using.

USE YourDatabaseName; — Replace with your database name

GO

EXEC sp_spaceused;

Data space, index space, and unallocated space are all included in the information about database size that will be returned as a result set. Don’t forget to substitute the actual name of the database you wish to inspect for YourDatabaseName. The sample output is given below.

Fig. 1: Sample Output for sp_spaceused system stored procedure

3. Using the sys.database_files View

Launch a fresh SSMS query window. Enter the following query and run it,

SELECT *
FROM sys.database_files
WHERE database_id = [database_id]

This query will give you a table that lists all of the data and log files in the database along with their sizes. Here’s the sample output given below.

Fig. 2: Sample Output for sys.database_files view

Using sys.master_files Script

You can use this script in an SSMS query window as well.

SELECT
    name,
    size,
    size * 8/1024 'Size (MB)',
    max_size
FROM sys.master_files;

Fig. 3: Sample output for sys.master_files script

4. Using T-SQL Queries

T-SQL queries can be used to find out the size of the database.

USE YourDatabaseName; — Replace with your database name

GO

— For data file size

SELECT
    name AS FileName,
    size * 8 / 1024 AS FileSizeMB
FROM sys.master_files
WHERE type = 0 AND database_id = DB_ID();

— For log file size

SELECT
    name AS FileName,
    size * 8 / 1024 AS FileSizeMB
FROM sys.master_files
WHERE type = 1 AND database_id = DB_ID();

YourDatabaseName should be changed to the name of your database. Here is an example of the output assuming you have a database called “SampleDB”,

Data file size query output

FileName	FileSizeMB
SampleDB	5000

Log file size query output

FileName	FileSizeMB
SampleDB_log	1024

Please be aware that the actual file sizes will change depending on the size and setup of your database. The “FileSizeMB” column in the example output displays the file’s megabyte (MB) size. The placeholders “SampleDB” and “SampleDB_log” refer to your data and log files’ actual names.

Is It Possible to Automatically Check Each Database’s Size Across Every SQL Server Instance?

Yes, you can cycle through every database on an SQL Server instance and extract their sizes using T-SQL scripts in conjunction with dynamic SQL. The sys. databases system view is commonly used to obtain a list of databases, after which size queries for each database are run.

Conclusion

There are several ways to find out the size of a database’s data and log files, including SQL Server Management Studio, T-SQL queries, and system-stored procedures like sp_spaceused. Administrators can plan database capacity, optimize performance, and perform general database maintenance by routinely measuring and assessing database sizes.