Check Database Size in SQL SERVER

This blog describes how to know database size in SQL SERVER.

Sometimes we need to know how much disk space is used by databases in SQL SERVER. There are multiple ways to know the database size in SQL SERVER.

1. Using Table Sys.master_files
2. Using Stored Proc sp_spaceused
3. Using Manual Option in SSMS

Using Table Sys.master_files

This is one option by which we can know the database size. The below query uses two tables of databases which contain database ID, Name, etc, and another table master_files which contain size columns that hold the size of the database. By using Inner join(database ID) we are getting database size. Both tables are present in master database.

SELECT sys.databases.name, 
       CONVERT(VARCHAR, SUM(size)*8/1024) + ' MB' AS [Total disk space]
FROM sys.databases 
JOIN sys.master_files 
ON sys.databases.database_id = sys.master_files.database_id
GROUP BY sys.databases.name
ORDER BY sys.databases.name;

See below Figure 1 after executing the above query which gives all the databases with their sizes.

 

Figure 1: Get Database Size

Using Stored Proc sp_spaceused 

This is the second option to know the database size. Here we will call the stored procedure sp_spaceused which is present in the master database. This one helps to know the size of the current database.

EXEC sp_spaceused;

After calling above stored procedure it shows below in Figure 2 which contains a column called database_size surrounded by a red mark.

Figure 2: Get Database size by SP

Using the Manual Option in SSMS

This is another option to know the database size. To know the size Go to Server Explorer -> Expand it -> Right click on Database -> Choose Properties -> In the popup window choose General tab ->See Size property which is marked(red) in Figure 3.

 

Figure 3: Manual option to get Database size

Hope it helps you to get database size in SQL SERVER.

Happy Coding!!