SQL Server Performance Forum – Threads Archive

Arithmetic overflow occured

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 .

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 Madhivanan

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.

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
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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?

I traced in profiler and found its generating by spid9,and writing to evenlog. 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. its writin to sqlserver log many times in a minute <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> . "arithmetic overflow occured"

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 .

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
AS
SET NOCOUNT ON WHILE 0 = 0
BEGIN WAITFOR DELAY ’00:00:10′
EXEC RefreshUserActivity END any idea?

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.

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?

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

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:
DECLARE @CNT INT
SET @CNT = 1
WHILE @CNT < 100
BEGIN
WAITFOR DELAY ’00:00:10′
EXEC RefreshUserActivity
SET @CNT = @CNT + 1
END 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.

CREATE PROC RefreshUserActivity
AS
INSERT UserActivity
SELECT suser_sname(sid) as name,
spid,
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 ??

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.

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.

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.

Thsi activity was workin from from 2001 , til last week. Its sql 7.00 . 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=’

‘ /> 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> ******Here comes the table structure ***** CREATE TABLE [dbo].[UserActivity] ( [name] [varchar] (30) NULL , [spid] [smallint] NOT NULL , [db] [varchar] (30) NULL , [hostname] [varchar] (30) NOT NULL , [program_name] [varchar] (30) NOT NULL , [login_time] [datetime] NOT NULL , [last_batch] [datetime] NOT NULL , [closed_by] [datetime] NULL , [net_address] [varchar] (12) NULL , [net_library] [varchar] (12) NULL ) ON [PRIMARY] GO CREATE UNIQUE CLUSTERED INDEX [XXX] ON [dbo].[UserActivity]([login_time], [spid]) ON [PRIMARY] GO Rajiv SQL-DBA

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.

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.

Well, maybe the information inserted into the column is different on the other servers? OPnly trying to help …

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 .

I am all set now. used followin steps use dba Go Kill 9 Go then i restarted process from OSQL . took 1 minute to complete it. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> . But thsi is a v good topic of discussion. any comments will be appreciated. anyway special thanks to Adriaan for his sugessions . Rajiv SQL-DBA

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

‘ />]

]]>