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? . Please tel me if u had any exp before.

its writin to sqlserver log many times in a minute . "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.