Database Sizes error; Arithmetic overflow error converting expression to data type int

Question

I am using the below query to pull back database sizes for our SQL instances.

This cannot pull data larger than 2TB back because of the maximum number the int data type can pull back. How do I go about altering the query to return the database size, no matter how large the dbs?

SELECT      
sys.databases.name,  
CONVERT(VARCHAR,SUM(size) * 8/1024) +' MB' AS [Total_Size]  
FROM        sys.databases   
JOIN        sys.master_files  
ON          sys.databases.database_id=sys.master_files.database_id  
GROUP BY    sys.databases.name  
ORDER BY    sys.databases.name

asked 2021-10-01 by Daniel Nash

---

Answer

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

answered 2021-10-01 by Andy Mallon