You SUM up all the sizes, then do the multiplication (then division), but along the way if the numbers get too big, you can overflow the int
limit. There are a few ways you can work around that.
Primarily, you want to convert your numbers to a bigger datatype before doing the math to get the larger scale. Then convert again for your display formatting, if you're doing that in T-SQL, and don't have an app to do it.
A few cautionary notes.
float
is just an estimate. Don't use it if you need actual numbers. It does some internal rounding that can bite you, particularly when doing math. Note that if you just multiply an int
by 1.0
it will implicitly convert to an float
, not numeric
- Be sure to specify the length on varchar. Declaring varchar without length can have surprising results.
- I also added table aliases to make the code more readable for a future you.
sys.master_files
will contain the startup size for tempdb. It will not have the current size. To get the current size, you'll need to look at the metadata in the database itself by querying tempdb.sys.database_files
.
I maintain an open source DBA database that contains a stored procedure that does all of this for you as well. You can look at Check_FileSize
on GitHub and the corresponding blog post
I've stepped through some of the details in this query, with comments in line. I'll let the comments & query stand alone in unpacking the solution, but the last two columns will give you two different options, depending on whether you need to maintain a decimal place or not. Decimals can be very helpful if you convert into larger units, like GB or TB.
SELECT
DbName = d.name,
--This will throw an int overflow
--TotalFileSizePagesInt = SUM(mf.size),
--This will do an implicit conversion to float
--It's kind of lazy, but easy. But also confusing & magical to the reader.
TotalFileSizePagesFloat = SUM(mf.size*1.0),
--This will do an explict numeric conversion
--This is a bit more typing but crystal clear to the reader.
TotalFileSizePagesNumber = SUM(CONVERT(numeric(15,2), mf.size)),
--This will do an explict numeric conversion, plus the math conversion to MB
--Note that the math "moves" the decimal to more than 2 digits
TotalFileSizeMBNumber = SUM(CONVERT(numeric(15,2), mf.size)*8/1024),
--We can maintain 2 decimals just by converting again.
TotalFileSizeMBNumberRounded = CONVERT(numeric(15,2),(SUM(CONVERT(numeric(15,2), mf.size)*8/1024))),
--Or, if we don't need to maintain decimals, we can just use bigint in the first place
TotalFileSizeMBBigInt = SUM(CONVERT(bigint, mf.size)*8/1024),
--And now we can add your "MB" label back in
TotalFileSizeMBNumberWithLabel = CONVERT(varchar(50),CONVERT(numeric(15,2),(SUM(CONVERT(numeric(15,2), mf.size)*8/1024)))) + ' MB',
TotalFileSizeMBBigingWithLabel = CONVERT(varchar(50),SUM(CONVERT(bigint, mf.size)*8/1024)) + ' MB'
FROM sys.databases AS d
JOIN sys.master_files AS mf
ON d.database_id = mf.database_id
GROUP BY d.name
ORDER BY d.name
``