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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

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

  1. 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. Required fields are marked *