Friday, May 29, 2015

Get all the database and their size from a server


SELECT    DB_NAME(db.database_id) as DatabaseName,
    CAST((CAST(mfrows.RowSize as decimal(18,2)) * 8) /POWER(1024,2) AS DECIMAL(18,2)) as DataFileSizeGB,
    CAST((CAST(mfrows.RowSize as decimal(18,2)) * 8) /POWER(1024,1) AS DECIMAL(18,2)) as DataFileSizeMB,
    CAST((CAST(mflog.LogSize as decimal(18,2)) * 8) /POWER(1024,2) AS DECIMAL(18,2)) as LogFileSizeGB,
    CAST((CAST(mflog.LogSize as decimal(18,2)) * 8) /POWER(1024,1) AS DECIMAL(18,2)) as LogFileSizeMB
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
ORDER BY DB_NAME(db.database_id)

No comments: