Query to find the size of the database and database file

Hi All,

Query to find the size of the database and database file is a simple and most common requirement. There are many ways & techniques to do this. Here are ‘just’ some of them:

Technique 1:

sp_helpdb

Gives you the database size and certain more properties of it:

1_SQL_Server_Query_to_find_the_size_of_the_database_and_database_file

Technique 2: For a specific database:

sp_helpdb'adventureworks'

And you get the following output: (this one gives you the database and data file information for a specific database)

2_SQL_Server_Query_to_find_the_size_of_the_database_and_database_file

Technique 3: be in the context of the database and run sp_spaceused

sp_spaceused

This is what you get:

   

3_SQL_Server_Query_to_find_the_size_of_the_database_and_database_file

Technique 4: use sys.master_files

SELECTDB_NAME(database_id)AS DatabaseName,
Name AS LogicalName,
size*8/1024 Size_in_MB,
max_size
FROMsys.master_files
WHEREDB_NAME(database_id)='AdventureWorks'
GO

4_SQL_Server_Query_to_find_the_size_of_the_database_and_database_file

HTH !

If there are more ways, do post a comment, would like to learn more. Thanks !

 

Regards

Rahul Sharma

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

5 Comments on “Query to find the size of the database and database file”

  1. select * from sys.dm_db_file_space_usage will only return the information for tempdb. This is by design

  2. HI All,

    I want to know the size of my SSAS database, the above queries will not give any info regarding SSAS databases and cubes.

    Please help

Leave a Reply

Your email address will not be published.