Idea check

Hi,

 

I am getting this error on some servers:

 

Arithmetic overflow error converting expression to data type int

 

I have downloaded and installed what I think is the latest version from site.

  • Can you please share with us more details, such as the exact configuration of your system, exact steps to reproduce this error, and perhaps screen capture images?

    Perhaps you can first try using SQL Check with an example demonstration database (such as AdventureWorks)?
  • In reply to Peter V.:

    Specifically, can you please share with us which version of SQL Check you are using?
  • In reply to Peter V.:

    I am also having this problem with the arithmetic overflow error. I am using SQL Check version 3.5.2012.1005. I ran profiler and caught the error. It occurs in the code below and is specifically in this line:


    CAST((@@CPU_BUSY + @@IO_BUSY + @@IDLE) AS BIGINT) as TotalCPU,


    declare @version char
    SELECT @version = Left(cast(SERVERPROPERTY('productversion') as varchar), 1)
    declare @LgReads bigint
    select @LgReads=cntr_value from master.dbo.sysperfinfo where counter_name='Page lookups/sec'
    declare @BatchReq bigint
    select @BatchReq=isnull(sum(convert(bigint,cntr_value)),0) from master..sysperfinfo where ((lower(object_name) like lower('%SQL Statistics%')) and (lower(counter_name) = 'batch requests/sec'))
    declare @SqlComp bigint
    select @SqlComp=isnull(sum(convert(bigint,cntr_value)),0) from master..sysperfinfo where ((lower(object_name) like lower('%SQL Statistics%')) and (lower(counter_name) = 'sql compilations/sec'))
    declare @SqlRecomp bigint
    select @SqlRecomp=isnull(sum(convert(bigint,cntr_value)),0) from master..sysperfinfo where ((lower(object_name) like lower('%SQL Statistics%')) and (lower(counter_name) = 'sql re-compilations/sec'))
    declare @Trans bigint
    select @Trans=isnull(sum(convert(bigint,cntr_value)),0) from master..sysperfinfo where ((Lower(object_name) like Lower('%databases%')) and (Lower(counter_name) = 'transactions/sec') and (Lower(instance_name) <> '_total'))
    declare @cpu bigint
    select top 1 @cpu = convert(xml,record).value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
    from sys.dm_os_ring_buffers
    where ((ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR') and (record like '%<SystemHealth>%'))
    order by timestamp desc
    select
    @cpu as 'CPU',
    CAST(@@CPU_BUSY AS BIGINT) as CPUBusy,

    CAST((@@CPU_BUSY + @@IO_BUSY + @@IDLE) AS BIGINT) as TotalCPU,

    sum(convert(bigint, case when wait_type like 'LCK%'
    then wait_time_ms - signal_wait_time_ms else 0 end)) as Locks,

    sum(convert(bigint, case when wait_type like 'LATCH%' or wait_type like 'PAGELATCH%' or wait_type like 'PAGEIOLATCH%'
    then wait_time_ms - signal_wait_time_ms else 0 end)) as Reads,

    sum(convert(bigint, case when wait_type like '%IO_COMPLETION%' or wait_type='WRITELOG'
    then wait_time_ms - signal_wait_time_ms else 0 end)) as Writes,

    sum(convert(bigint, case when wait_type in ('NETWORKIO','OLEDB')
    then wait_time_ms - signal_wait_time_ms else 0 end)) as Network,

    sum(convert(bigint, case when wait_type like 'BACKUP%' or wait_type='DISKIO_SUSPEND'
    then wait_time_ms - signal_wait_time_ms else 0 end)) as 'Backup',

    sum(convert(bigint, case when wait_type='PSS_CHILD'
    then wait_time_ms - signal_wait_time_ms else 0 end)) as 'Cursor',

    sum(convert(bigint, case when wait_type='ASYNC_DISKPOOL_LOCK' or wait_type='ASYNC_IO_COMPLETION' or wait_type='IO_COMPLETION' or wait_type like 'PAGEIOLATCH%'
    then wait_time_ms - signal_wait_time_ms else 0 end)) as 'I/O',

    sum(convert(bigint, case when wait_type='XACTLOCKINFO' or wait_type like 'LCK%'
    then wait_time_ms - signal_wait_time_ms else 0 end)) as 'Lock',

    sum(convert(bigint, case when wait_type='CMEMTHREAD' or wait_type like 'RESOURCE_SEMAPHORE%' or wait_type='SOS_RESERVEDMEMBLOCKLIST'
    then wait_time_ms - signal_wait_time_ms else 0 end)) as 'Memory',

    sum(convert(bigint, case when wait_type like 'PAGELATCH%'
    then wait_time_ms - signal_wait_time_ms else 0 end)) as 'Non-I/O Page Latch',

    sum(convert(bigint, case when wait_type like 'LATCH%' or wait_type like 'TRAN_MARKLATCH%'
    then wait_time_ms - signal_wait_time_ms else 0 end)) as 'Non-Page Latch',

    sum(convert(bigint, case when wait_type='ASYNC_NETWORK_IO ' or wait_type like 'BROKER%' or wait_type='CURSOR' or wait_type='CXPACKET' or wait_type='EXCHANGE'
    or wait_type='PAGESUPP' or wait_type like 'PREEMPTIVE_%' or wait_type like 'SLEEP%' or wait_type='SOS_SCHEDULER_YIELD' or wait_type='TEMPOBJ'
    or wait_type='THREADPOOL' or wait_type='XE_%'
    then wait_time_ms - signal_wait_time_ms else 0 end)) as 'Other',

    sum(convert(bigint, case when wait_type='RESOURCE_QUERY_SEMAPHORE_COMPILE'
    then wait_time_ms - signal_wait_time_ms else 0 end)) as 'Query Compilation',

    sum(convert(bigint, case when wait_type='LOGBUFFER' or wait_type='LOGMGR' or wait_type='WRITELOG'
    then wait_time_ms - signal_wait_time_ms else 0 end)) as 'Transaction Log',

    @@TOTAL_READ AS PhReads, @@TOTAL_WRITE AS PhWrites, ISNULL(@LgReads, 0) AS LgReads,

    @BatchReq AS 'BatchReq',
    @SqlComp AS 'SqlComp',
    @SqlRecomp AS 'SqlRecomp',
    @Trans AS 'Trans'
    from
    sys.dm_os_wait_stats
  • In reply to Lee Linares:

    There is a newer version of SQL Check (3.6) that fixes this problem which is available on the Idera website.
  • In reply to Peter V.:

    Hi,

    We are using Idera's SQL Check in Version 3.6 (I specifically downloaded & reinstalled SQL Check again today to be sure to have the newest Version).

    And we get the following Error Message since yesterday (SQL Check 3.6 had been working flawlessly for > 6 Month on our SQL Server 2008 R2 System until then).

    Also, there was nothing done to the SQL Server System in the last days (no new Patch Level, no new Version, ...):

     

    Thanks to advise on a solution.

    Best Regards

     

     

     

  • In reply to fiechterjm:

    Can you please share with us the application log file? We use TracerX for logging. The log files are saved to the application data folder. Refer to the forum posts "In which folder is user application data stored?" and "How to send log files for free tools to IDERA?".

  • In reply to Peter V.:

    Hi Peter,

    The Problem has been resolved by "chance". We had to reboot the DB Server and thus all the Counters were reseted, particularly the "Cumulative ..." Counters (eg. Cumulative Wait Time).
    Now all those Counters are back within the INT realm !

    Best Regards
  • In reply to Peter V.:

    Log File was sent by E-Mail to iderafreetools_49b7@sendtodropbox.com
  • In reply to fiechterjm:

    We received your files, and we analyzed them thoroughly. Indeed, as you suggested, the problem appears to be due to cumulative counters overrunning their limits on busy instances over a long time. We added this issue to our issue tracking system.