Sunday, August 25, 2019

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.

  Sql Server : Check size of database

Options 2 : Using sp_helpdb Stored Procedure
2nd 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:

Sql Server : Check size of database


sp_helpdb 'aspmantra'
Result:

Using sp_helpdb Stored Procedure



Options 3: Using sys.database_files
3rd 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:

Using sys.database_files



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:
 Using sys.master_files


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:

using sp_databases Stored Procedure



Options 6: Using sp_spaceused Stored Procedure
6th 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:

Using sp_spaceused Stored Procedure






No comments:

Post a Comment