Arithmetic overflow error converting expression to data type int

[ia01]

Hi,

Just trying to run the following query, it was working for a while, suddenly started getting "Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type int." Any suggestion please?

select COUNT(*), SUM(SS.ItemSize)/1024 'size in MB' from saveset SS

inner join ArchivePoint AP on SS.ArchivePointIdentity = AP.Archivepointidentity

inner join gsgs2evfsql01.EnterpriseVaultDirectory.dbo.ArchiveView EVDAV on AP.ArchivePointId = EVDAV.VaultEntryId

where EVDAV.ArchiveName = 'archive'

and SS.ArchivedDate > '2010-10-12'

and SS.ArchivedDate < '2012-12-12'

Many Thanks

[ia01]

Funny enough if I change the query date range to today's date it works! However i have been using the same last few weeks with 2012-12-12 and it used to work before.

and SS.ArchivedDate > '2010-10-12'

and SS.ArchivedDate < '2012-10-24'

[JesusWept3 · Accepted solution]

You're going to have to CAST the item to a bigger int

SUM(CONVERT(bigint,SS.ItemSize))

[Rob_Wilcox1]

My suggestion would be that this:

SUM(SS.ItemSize)/1024

isn't returning an integer.

[JesusWept3]

The explanation is that an INT can grow to 2,147,483,647 and you are THEN dividing by 1024, so thats basically 2TB it can count up to in a regular INT, a BigInt goes up to 9,223,372,036,854,775,807 which is 8 zettabytes, and I don't think you'll get close to that size at any given point
 

[ia01]

Thanks

SUM(CONVERT(bigint,SS.ItemSize)) worked perfectly as archived Data is just over 2TB now for that particular archive :)

Thanks again for your help, really appreciate!