Get actual database size in SQL Server 2012 in GB?

Question

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....

Answer 1

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.

Answer 2

Nat is right. You need to divide by 1024 again. To make things easier to read I like to see the log and data files labeled. As well as including the file sizes in each format.

SELECT 
    DB_NAME(mf.database_id) AS 'DB Name', 
    name AS 'File Logical Name',
    'File Type' = CASE WHEN type_desc = 'LOG' THEN 'Log File' WHEN type_desc = 'ROWS' THEN 'Data File' ELSE type_desc END,
    mf.physical_name AS 'File Physical Name', 
    size_on_disk_bytes/ 1024 AS 'Size(KB)', 
    size_on_disk_bytes/ 1024 / 1024 AS 'Size(MB)',
    size_on_disk_bytes/ 1024 / 1024 / 1024 AS 'Size(GB)'
FROM 
    sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs 
    JOIN sys.master_files AS mf 
        ON mf.database_id = divfs.database_id 
            AND mf.file_id = divfs.file_id
ORDER BY 
    DB_NAME(mf.database_id)