Check Database Size in SQL SERVER

Sometimes we need to know how much disk space 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 database size. Below query uses two tables databases which contains database ID, Name etc and another table master_files which contains size columns holds size of 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 above query which gives all the databases with their sizes.

 

 

Figure 1: Get Database Size

 

Using Stored Proc sp_spaceused 

 

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

exec sp_spaceused

After calling above stored procedure it shows  below Figure2 which contains column called database_size surrounded by red mark.

 

Figure 2: Get Database size by SP

 

Using Manual Option in SSMS

 

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

 

 

Figure 3: Manual option to get Database size

 

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

 

Happy Coding!!