Skip to Content

Arithmetic overflow occurred on Sybase query

Hi

This query works on QA, but fails on DEV with "Arithmetic overflow occurred".

SELECT lct_admin('num_logpages', dbid) UsedPages, lct_admin('logsegment_freepages',dbid) FreePages,100.0 - lct_admin('num_logpages', dbid) * 100.0 /(lct_admin('logsegment_freepages',dbid) + lct_admin('num_logpages', dbid)) PctFree FROM master..sysdatabases WHERE name = 'MED'

I need to get it working, as it is build into a script that need to execute.

Please advise?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jul 25, 2017 at 05:31 PM

    Hi Willem,

    Well, lct_admin() returns integers while the litteral 100.0 will be assigned a numeric(4,1) datatype.

    The overflow is probably coming from the "lct_admin('num_logpages', dbid) * 100.0 " part of the equation.

    What is the largest value you have for select lct_admin('num_logpages', dbid) from master..sysdatabases?
    Would 100 times that amount be larger than MAXINT?

    One approach for avoiding the overflow would be to do the division first (resulting in a smaller value) before multiplying by 100.0
    Another would be to convert to a datatype with a bigger range. I'd think FLOAT would work well here. Does the following avoid the error?

    declare @hundred float
    select @hundred = 100.0
    SELECT
    lct_admin('num_logpages', dbid) UsedPages,
    lct_admin('logsegment_freepages',dbid) FreePages,
    @hundred - lct_admin('num_logpages', dbid) * @hundred /(lct_admin('logsegment_freepages',dbid) + lct_admin('num_logpages', dbid)) PctFree
    FROM master..sysdatabases
    WHERE name = 'MED'

    -bret

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 31, 2017 at 10:51 AM

    Hi Bret

    Thanks for your response.

    The largest value in "lct_admin('num_logpages', dbid) * 100.0 " is: 4294925009.

    I notice this is larger than: "INTEGER A signed 32-bit integer with a range of values between -2147483648 and 2147483647"

    The float select statement still fails:

    3> declare @hundred float select @hundred = 100.0 SELECT lct_admin('num_logpages', dbid) UsedPages, lct_admin('logsegment_freepages',dbid) FreePages, @hundred - lct_admin('num_logpages', dbid) * @hundred /(lct_admin('logsegment_freepages',dbid) + lct_admin('num_logpages', dbid)) PctFree FROM master..sysdatabases WHERE name = 'MED'4> 5> 6> 7> 8> 9> 10> 11> go (1 row affected) Msg 3606, Level 16, State 4: Server 'MED', Line 5: Arithmetic overflow occurred. (0 rows affected)

    If I broke up the SELECT script, it fails on this portion:

    SELECT 100.0 - lct_admin('num_logpages', dbid) * 100.0 /(lct_admin('logsegment_freepages',dbid) + lct_admin('num_logpages', dbid)) PctFree FROM master..sysdatabases WHERE name = 'MED' go

    It executes successfully if it executes the division first:

    SELECT 100.0 - lct_admin('num_logpages', dbid) /(lct_admin('logsegment_freepages',dbid) / 100.0 + lct_admin('num_logpages', dbid)) PctFree FROM master..sysdatabases WHERE name = 'MED' go

    I will have to recommend to have the script updated with this changes.

    Add comment
    10|10000 characters needed characters exceeded