Search code examples
sql-server

Get actual database size in SQL Server 2012 in GB?

I have a database in SQL Server, to get the size of database in 'GB', what is the query I should use?

Query I tried is:

select 
    d.name, m.size * 8 / 1024
from 
    sys.master_files m 
join
    sys.databases d on d.database_id = m.database_id and m.type = 0

But it is not returning the size in GB....

Solution

  • You need to divide by 1024 again.

    select d.name, m.size * 8 / 1024 / 1024
from sys.master_files m JOIN sys.databases d ON d.database_id = m.database_id and m.type =0

    However this will round to the nearest whole GB (i.e. integer) You will need to cast as a (numeric, float, decimal, double, etc.)

    If you run:

    SELECT physical_name, size * 8 / 1024 / 1024  FROM sys.database_files WHERE TYPE = 0

    That will give you the information for the database you are connected to, not all databases on the instance.