CODEWITHSUNDEEP
sql-server
svsLm
svsLm

Reputation: 127

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

Upvotes: 7

Views: 77554

Answers (4)

Francesco Mantovani
Francesco Mantovani

Reputation: 12377

A noiseless solution:

 SELECT d.NAME
    ,ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0) Size_MBs
    ,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.NAME
ORDER BY d.NAME

Upvotes: 0

Ali Asad
Ali Asad

Reputation: 11

Slightly modified code to give size as xx TB xx GB xx MB

select 
    d.name as 'database'
    , mdf.physical_name as 'mdf_file'
    , mdf.size
    , CONVERT(VARCHAR(10), (mdf.size * 8) / (1024 * 1024 * 1024)) + ' TB '
    + CONVERT(VARCHAR(10), ((mdf.size * 8) % (1024 * 1024 * 1024)) / (1024 * 1024)) + ' GB '
    + CONVERT(VARCHAR(10), (((mdf.size * 8) % (1024 * 1024 * 1024)) % (1024 * 1024)) / 1024) + ' MB '
    , ldf.physical_name as 'log_file'
    , ldf.size
    , CONVERT(VARCHAR(10), (ldf.size * 8) / (1024 * 1024 * 1024)) + ' TB '
    + CONVERT(VARCHAR(10), ((ldf.size * 8) % (1024 * 1024 * 1024)) / (1024 * 1024)) + ' GB '
    + CONVERT(VARCHAR(10), (((ldf.size * 8) % (1024 * 1024 * 1024)) % (1024 * 1024)) / 1024) + ' MB '
from sys.databases d
inner join sys.master_files mdf on 
    d.database_id = mdf.database_id and mdf.[type] = 0
inner join sys.master_files ldf on 
    d.database_id = ldf.database_id and ldf.[type] = 1
order by d.name

Sample output

database    mdf_file                Size            log_file                        Size
Database    C:\MSSQLDB\Database.mdf 0 TB 0 GB 5 MB  C:\MSSQLLog\Database_log.ldf    0 TB 0 GB 2 MB

Upvotes: 1

Chris Albert
Chris Albert

Reputation: 2507

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)

Upvotes: 7

Nat
Nat

Reputation: 164

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.

Upvotes: 12

Related Questions