How to get database size in SQL Server Enterprise edition

Question

I'm getting an error with regard of this query

SELECT
    DB.name,
    SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024.0  ELSE 0 END) AS DataFileSizeMB,
    SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024.0 ELSE 0 END) AS LogFileSizeMB,
    SUM(mf.size* 8 / 1024.0) AS TotalSizeMB,    
    SYSDATETIME() AS Datelogged
FROM
    sys.master_files MF
JOIN 
    sys.databases DB ON DB.database_id = MF.database_id
WHERE 
    DB.source_database_id IS NULL
GROUP BY 
    DB.name
ORDER BY 
    Datelogged DESC

I get an error:

Arithmetic overflow error converting expression to data type int

I'm running this on SQL Server 2016 Enterprise edition. I've tried to run this on Standard and other lower editions and it'll work. Can someone tell what's wrong with this code and what should be the correct one. I do not own this code, I've just copied it from another site.

Action taken : I've try to convert this into bigint data type but still no avail.

Thank you very much

I think you need to CAST the numeric values to decimal, see [this question](https://stackoverflow.com/questions/18014392/select-sql-server-database-size) — Hooman Bahreini, Sep 09 '19 at 05:24

score 0 · Accepted Answer · edited Sep 09 '19 at 07:47

0

instead of MF.size * 8 use MF.size * 8.0

edited Sep 09 '19 at 07:47

PV8

5,799
7
43
87

answered Sep 09 '19 at 05:32

Veera Pallati

31
2

How to get database size in SQL Server Enterprise edition

1 Answers1