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.
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'
sys.database_files
andsys.filegroups
catalog views instead of the compatability views. DocumentationSELECT sf.*
instead ofSELECT sum(size)
, to validate that you're getting back the number of rows you expect, and the values are what you expect.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';
.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