Wednesday, August 27, 2014

Total user Database size in GB's




--- To fetch the User databases  Size in GBs use below query

SELECT    
DB_NAME(db.database_id) DatabaseName,    
(CAST(mfrows.RowSize AS FLOAT)*8)/1024/1024+(CAST(mflog.LogSize AS FLOAT)*8)/1024/1024 DBSizeGB
FROM sys.databases db    
LEFT JOIN (SELECT database_id,
                  SUM(size) RowSize
            FROM sys.master_files
            WHERE type = 0
            GROUP BY database_id, type) mfrows
    ON mfrows.database_id = db.database_id    
LEFT JOIN (SELECT database_id,
                  SUM(size) LogSize
            FROM sys.master_files
            WHERE type = 1
            GROUP BY database_id, type) mflog
    ON mflog.database_id = db.database_id    
        where db.database_id > 5
ORDER BY 1 DESC


---- Results are as below