SQL Server Performance

Arithmetic overflow occured

Discussion in 'General DBA Questions' started by gkrishn, Mar 16, 2005.

  1. gkrishn New Member

    My application of OS is full with error "Arithmetic overflow occured" produced from SQL server. For last 4 days its flooding application log with same error.This happens each and every minute.So my appli log is getin full al the time.

    Anyone have any idea why Arithmetic overflow occured from SQl server Side.Mine is production server.I am the DBA for that. Please advice .
  2. Madhivanan Moderator

    I think some trasactions are trying to store the big data to the int or integer type columns which are not capable of holding that

  3. Adriaan New Member

    Could basically be any kind of calculation in a procedure/trigger/UDF that doesn't use appropriate variable types, or doesn't check if the result is unacceptable for a table column where it is supposed to be stored.

    Could be the coding does not anticipate going over a certain amount of iterations in a WHILE loop, with a counter that is of an insufficient variable type.
  4. satya Moderator

    Arithmetic overflow occurs due to any calculations that involves anything divided by zero, which leads to infinite in mathematical terms and SQL will not be able to decide the correct value to result.

    So best option is to use PROFILER and see where in the query it is occuring and try to fine tune it.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. gkrishn New Member

    Satya, i cant tune queries, as its not i am supposed to do here.

    Can i do anythin serverl level, set arithabort,ignore anything? is tht a server level setting?

    its spid9 gerenating error.
    i ran sp_who to see whts it. its s background process runnin under sa account .

    any idea?
  6. gkrishn New Member

    I traced in profiler and found its generating by spid9,and writing to evenlog.<br />But no sql username,NT username,applica name,transaction i could find along with it.So i think its not invokin by any user. is it anythin happenin internal? <img src='/community/emoticons/emotion-6.gif' alt=':(' /> . Please tel me if u had any exp before. <br /><br />its writin to sqlserver log many times in a minute <img src='/community/emoticons/emotion-6.gif' alt=':(' /> . "arithmetic overflow occured"<br /><br /><br />
  7. gkrishn New Member

    Spid9- i found it always with my DBA database.
    i checkd sqlserver logs,i saw spid9 always associated with DBA database. (spid9-starting DBA database,spid9-rollforward transaction in DBA,spid9-rollback transaction in DBA database etc..)

    So i think its some background proces assoctiatedw with DBA database. If its some BG process why shoud it generate arithmentic overflow error .

  8. gkrishn New Member

    Hey i am almost close to solution .

    i saw one procedure callin recursivly itself .it uses while 0=0 .This can be a problem? it was running fine for last 2 years but .

    I think this procedure can be the problem .

    CREATE PROC MonitorUserActivity

    WHILE 0 = 0

    WAITFOR DELAY '00:00:10'
    EXEC RefreshUserActivity


    any idea?
  9. Adriaan New Member

    WHILE 0=0 is definitely an infinite loop: a trick to run the RefreshUserActivity activity every 10 seconds, until the connection is killed.

    Execute EXEC RefreshUserActivity from QA and see if you get more detailed error information on the Messages tab.
  10. gkrishn New Member

    MonitorUserActivity procedure is running automatically after startup.Thsi wil call other procedure each 10 seconds. This methord they are using for 2 years.But problem occuring only for last 4 days.

    I traced profiler. Error is reporting when MonitorUserActivity procedure is executing.Can anyone analyse this Procedure?

  11. gkrishn New Member

    I have one suggestion, Increasing delay wil solve the pbm ? I think the other procedure takes more than 10 sec to complete.
  12. Adriaan New Member

    quote:Originally posted by gkrishn

    MonitorUserActivity procedure is running automatically after startup.Thsi wil call other procedure each 10 seconds. This methord they are using for 2 years.But problem occuring only for last 4 days.

    I traced profiler. Error is reporting when MonitorUserActivity procedure is executing.Can anyone analyse this Procedure?
    Seems like you could use some training in debugging ...

    The MonitorUserActivity procedure is calling the RefreshUserActivity procedure every 10 seconds, and you are getting an error every 10 seconds. So if you have really given us the full script of the MonitorUserActivity procedure, which doesn't have anything remotely arithmetic to speak of, then it is obvious that the error is occurring in the RefreshUserActivity procedure, or in any procedure that is being called from the RefreshUserActivity procedure.

    Furthermore, you are not necessarily seeing the full error details. You should get the same error if you executed EXEC RefreshUserActivity directly in QA, and you might see some additional info on the Messages tab. Note that you could even get some info on the Messages tab with no error on the Results tab, meaning that the error was insignificant.

    You could also run this script:
    SET @CNT = 1
    WHILE @CNT < 100
    WAITFOR DELAY '00:00:10'
    EXEC RefreshUserActivity
    SET @CNT = @CNT + 1

    This mimics the MonitorUserActivity procedure, except that it runs 100 times and then stops. If there are no errors on the Messages tab, try increasing the number of iterations (WHILE @CNT < 200, etc.) and see what happens.
  13. gkrishn New Member

    CREATE PROC RefreshUserActivity
    INSERT UserActivity
    SELECT suser_sname(sid) as name,
    db_name(dbid) as db,
    hostname, program_name,
    login_time, last_batch,
    NULL, net_address, net_library
    from master..sysprocesses as P
    where suser_name(suid) NOT IN ('probe', 'BackUpUser')
    and spid not between 1 and 6
    AND NOT EXISTS (select * from UserActivity as U
    where P.login_time = U.login_time
    and P.spid = U.spid)

    UPDATE UserActivity
    SET last_batch = P.last_batch
    FROM master..sysprocesses as P
    WHERE UserActivity.login_time = P.login_time
    AND UserActivity.spid = P.spid
    AND UserActivity.last_batch <> P.last_batch

    UPDATE UserActivity
    SET closed_by = getdate()
    WHERE closed_by IS NULL
    AND NOT EXISTS (select * from master..sysprocesses as P
    where P.login_time = UserActivity.login_time
    and P.spid = UserActivity.spid)

    Adriaan ,any statement here which can cause Arithmetic overflow ??
  14. Adriaan New Member

    Nothing obvious in this procedure by itself. But obviously the definition of the table UserActivity - column sizes, triggers, ... - can be critical here.

    As I said, see what error details you can get by calling this procedure from QA, either once or in a loop. If at all possible, do this in the production database - it looks like a monitoring procedure, so there should be no problem if you add a few iterations. You may not be able to see the error in a test environment, unless you can duplicate the user activity from the live database.
  15. gkrishn New Member

    Same server i executed RefreshUserActivity from QA, ran without any errors .

    i believe this is becos of the proc MonitorUserActivity which runnin in infinite loop.
  16. Adriaan New Member

    Not sure what database environment you're working in, but there are two problems in case you're in SQL 2000:

    -1- SUSER_NAME() always returns NULL in SQL 2000 (see BOL). Use SUSER_SNAME() instead.

    -2- There is no column suid in sysprocesses. You probably need the sid column.

    The problem does not occur automatically, which leads me to believe that it is dependent on a varying measurement, like the number of connections for which details get inserted into your UserActivity table. Also the actual data getting inserted into your UserActivity table, with the question whether that data fits the column definitions (if you could just provide the script for UserActivity ...) and whether any triggers get fired that may cause the error.
  17. gkrishn New Member

    Thsi activity was workin from from 2001 , til last week. Its sql 7.00 .<br /><br />As i said, i executed RefreshUserActivity from QA, ran without any errors .That infinite loop or delay timer, could be makin issue .Still donno whts the issue.m goin to restart the process <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />see my other post<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=7599>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=7599</a><br /><br />******Here comes the table structure *****<br />CREATE TABLE [dbo].[UserActivity] (<br />[name] [varchar] (30) NULL ,<br />[spid] [smallint] NOT NULL ,<br />[db] [varchar] (30) NULL ,<br />[hostname] [varchar] (30) NOT NULL ,<br />[program_name] [varchar] (30) NOT NULL ,<br />[login_time] [datetime] NOT NULL ,<br />[last_batch] [datetime] NOT NULL ,<br />[closed_by] [datetime] NULL ,<br />[net_address] [varchar] (12) NULL ,<br />[net_library] [varchar] (12) NULL <br />) ON [PRIMARY]<br />GO<br /><br /> CREATE UNIQUE CLUSTERED INDEX [XXX] ON [dbo].[UserActivity]([login_time], [spid]) ON [PRIMARY]<br />GO<br /><br />Rajiv<br />SQL-DBA
  18. Adriaan New Member

    Check the column definitions for program_name, host_name, etc. in the UserActivity table - a lot of those are just too short compared to the columns in sysprocesses.

    You haven't confirmed if there are any triggers defined for the UserActivity table.
  19. gkrishn New Member

    I dont think that is the problem, same script is executing in other 5 servers without any pbm, you can also try same in ur local machine. V soon i am goin to restart my process. I wil update you on this.

    no triger associated to tht table.

  20. Adriaan New Member

    Well, maybe the information inserted into the column is different on the other servers? OPnly trying to help ...
  21. gkrishn New Member

    No, all are exactly same. Adriaan, this start from last weekonly .From 2001 it was workin fine.
    I executed RefreshUserActivity from QA, it executed without any errors and table got refreshed also with recent data .
  22. gkrishn New Member

    I am all set now. used followin steps<br />use dba<br />Go<br />Kill 9 <br />Go<br />then i restarted process from OSQL . took 1 minute to complete it. <img src='/community/emoticons/emotion-1.gif' alt=':)' /> .<br /><br /><br />But thsi is a v good topic of discussion. any comments will be appreciated. anyway special thanks to Adriaan for his sugessions .<br /><br />Rajiv<br />SQL-DBA
  23. Adriaan New Member

    Keeping my fingers crossed that the problem doesn't resurface.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page