Sql Server : Check size of database

Here in this article I am going to explain how we can check the size of database in Sql Server.

There are many options to determine the size of database, from all of these options I am going to explain 6 options in this article.

Options 1 : Using Database properties

We can get the size of database manually using database properties. To know size of database expand the databases and right click on database of which you want to know the size. Select the properties.

  

Options 2 : Using sp_helpdb Stored Procedure

2^nd option is using system store procedure. If we execute the sp_helpdb will return the list of all database. We can also pass argument (database name) to this store procedure, this will return only info that particular database.

 

sp_helpdb

Result:

sp_helpdb 'aspmantra'

Result:

Options 3: Using sys.database_files

3^rd option is using sys.database_files. This will return the info of selected database.

SELECT file_id, name as database_name, type_desc, physical_name,size * 8/1024 'db size (MB)', max_size FROM sys.database_files

Result:

Options 4: Using sys.master_files

4th option is using sys.master_files. Below given query will return the info all database.

SELECT DB_NAME(database_id) AS 'Database Name',Name,Physical_Name, (size*8)/1024 'database size(MB)' FROM sys.master_files

Result:

If want to know the info of particular database run the below given query:

SELECT DB_NAME(database_id) AS 'Database Name',Name,

Physical_Name, (size*8)/1024 'database size(MB)' FROM sys.master_files where DB_NAME(database_id)='aspmantra'

Options 5: using sp_databases Stored Procedure

5th option is using system store procedure sp_databases. This will return the database name, db size and remark of all database.

sp_databases

Result:

Options 6: Using sp_spaceused Stored Procedure

6^th option is using system store procedure sp_spaceused. This will return the info of particular database. So select the database of which information is required. This will returns 2 result sets.

sp_spaceused

Result: