what are the possible reasons for 'Arithmetic overflow error converting expression to data type int' in SQL server

Question

When this command is run, there is SQL server error

'Arithmetic overflow error converting expression to data type int'.

SELECT sum(size) FROM [dbname]..sysfiles sf, [dbname]..sysfilegroups sfg WHERE sfg.groupname = 'PRIMARY' AND sf.groupid = sfg.groupid

Because the file size is less than 16TB, bigint is not required. Still I tried to execute cast(size as bigint), the error persists.

SELECT sum(cast(size as bigint)) FROM [dbname]..sysfiles sf, [dbname]..sysfilegroups sfg WHERE sfg.groupname = 'PRIMARY' AND sf.groupid = sfg.groupid

So the question is, what are the reasons for arithmetic overflow error other than exceeding the file size limit of 16TB.

• Mr.Brownstone: Do you get the same error when you restructure the query to use a JOIN predicate like so? SELECT sum(size) FROM [dbname]..sysfiles AS sf JOIN [dbname]..sysfilegroups AS sfg ON sf.groupid = sfg.groupid WHERE sfg.groupname = 'PRIMARY'
• Mr.Brownstone: Also, if you are using SQL Server 2008 and above, you should use the sys.database_files and sys.filegroups catalog views instead of the compatability views. Documentation
• RDFozz: I would also suggest a test run with SELECT sf.* instead of SELECT sum(size), to validate that you're getting back the number of rows you expect, and the values are what you expect.
• Solomon Rutzky: Assuming SQL >= 2005, the syntax that you are using and the system catalog views were deprecated as of SQL Server 2005. Try the following: SELECT SUM(CONVERT(BIGINT, df.[size])) AS [Total] FROM sys.database_files df INNER JOIN sys.data_spaces ds ON ds.[data_space_id] = df.[data_space_id] WHERE ds.[name] = N'PRIMARY';. That's just an updated version of your 2nd query. If you get the error, try: SELECT CONVERT(BIGINT, SUM(CONVERT(BIGINT, df.[size]))) AS [Total] FROM sys.database_files df INNER JOIN sys.data_spaces ds ON ds.[data_space_id] = df.[data_space_id] WHERE ds.[name] = N'PRIMARY';.
• sepupic: Exec your code this way: SELECT sum(size *1.) FROM sysfiles sf, sysfilegroups sfg WHERE sfg.groupname = 'PRIMARY' AND sf.groupid = sfg.groupid to find out the final size without getting any error and then post it here please

Answer 1

-- casting using DECIMAL(38,2) throws exception, Size needs to be casted as bigint

select [SizeInMB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(38,2) ) from sys.master_files

-- This fix is sufficient, but cannot limit to 2 decimal digits

select [SizeInMB1] = ((SUM(CAST(Size AS BIGINT))* 8) / 1024.0) from sys.master_files

-- 2 decimal digits are not available in fix, as divide by 1024.0 converts to default 6 digits

select [SizeInMB2] = ((SUM(CAST(Size AS DECIMAL(38,2)))* 8) / 1024.0) from sys.master_files

-- so 2 casts are required to eliminate Arithmetic overflow exception and limiting 2 decimal digits

select [SizeInMB3] = CAST( ((SUM(CAST(Size AS BIGINT))* 8) / 1024.0) AS DECIMAL(38,2) ) from sys.master_files