5

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.

Improve this question
6
  • 1
    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
    Aug 21, 2017 at 21:22
  • 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
    – Mr.Brownstone
    Aug 21, 2017 at 21:32
  • 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.
    – RDFozz
    Aug 21, 2017 at 21:57
  • 1
    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';.
    – Solomon Rutzky
    Aug 22, 2017 at 2:18
  • 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
    – sepupic
    Aug 22, 2017 at 6:43

1 Answer 1

Reset to default
5

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.