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
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.
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
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
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.
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.
What do you think about this article?
If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.