Share via

T-SQL: Query Database Size

  • Article

Where the magic happens...

If you ever wondered whether there is any magical query, which can show how much space (disk space, that is) each database consume or how much space does the whole database server consume? Then you have come to the right place and your search for such query is over. Use the following queries to get disk memory usage of either individual database or the server itself.

And even though you haven't wondered about these specific queries, let me tell you this, "Size does matter!". It is very crucial to keep a tab on the size of your database(s) in order to keep related programs perform at their best. So instead of wasting any more time, let's take a look at these queries, shall we?

Get Size Of Individual Database

Execute the following query to get the size of each database in MBs. The query fetches the sizes of the Data File and Log File of each database on the server.

--Query to find size of all individual databases on SQL Server
with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select
    name,
    (select sum(size) from  fs where  type = 0 and fs.database_id = db.database_id) DataFileSizeInMB,
    (select sum(size) from  fs where  type = 1 and fs.database_id = db.database_id) LogFileSizeInMB
from sys.databases db

Result

This query fetches the result which is similar to the following screenshot:

Get Size Of Database Server

Execute the following query to fetch the size of the database server in GB as well as in MB. 

--total size of cumulative databases in GB. Querying: sys.master_files (Transact-SQL) 
--Link:  https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql
SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpaceInGB,
CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00)) As UsedSpaceInMB
FROM master.sys.master_files

Result

This query fetches the result which is similar to the following screenshot:

Additional Resources