Error when executing purge on Repserver data

[Mark Bersales]

I am seeing the below errors on my asemon logs.
2023/03/15 15:45:23.288 Purge REPSERVER - Purge Thread : archive, error in loop. com.sybase.jdbc4.jdbc.SybSQLException: Arithmetic overflow occurred.

com.sybase.jdbc4.jdbc.SybSQLException: Arithmetic overflow occurred.

    at com.sybase.jdbc4.tds.Tds.processEed(Tds.java:4229)
    at com.sybase.jdbc4.tds.Tds.nextResult(Tds.java:3346)
    at com.sybase.jdbc4.jdbc.ResultGetter.nextResult(ResultGetter.java:78)
    at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:303)
    at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:285)
    at com.sybase.jdbc4.jdbc.SybStatement.updateLoop(SybStatement.java:2763)
    at com.sybase.jdbc4.jdbc.SybStatement.executeUpdate(SybStatement.java:2747)
    at com.sybase.jdbc4.jdbc.SybStatement.executeUpdate(SybStatement.java:597)
    at asemon_logger.PurgeThread.archiveIndic(PurgeThread.java:80)
    at asemon_logger.PurgeThread.run(PurgeThread.java:246)

2023/03/15 15:45:23.288 Purge REPSERVER - Purge Thread - archive,Closing connection

My asemon_logger version is 3.1.6

[Jean-Paul Martin]

Hi,
I don't know this error (no change in Trends.xml since V3.1.6)

Can you start your asemon_logger with trace flag 112:
asemon_logger ... -d 112

You'll get more info on the indicator which threads overflow
Then send me the trace

Regards
JP

[Mark Bersales]

Hi JP,
Thanks for the prompt reply. I have attached the logfile for your reference.

[Jean-Paul Martin]

Hi Mark

I think the problem is with this query :

   declare @startts datetime 
   select @startts=isnull(dateadd(dd, 1, (select convert(varchar, max(dt),102) from ASESERVER_Trends where TrendID=61) ),'1900/01/01')   
   select 61, dt=dateadd(hh,datepart(hh, Timestamp), convert(datetime,convert(varchar,Timestamp,102)) ), Value=case when sum(1.*Reads+Writes) = 0 then 0 else sum(convert(numeric(20,0),IOTime)) / sum(convert(numeric(20,0),1.*Reads+Writes)) end 
   from ASESERVER_DevIO     
   where Timestamp >= @startts     
   and Timestamp < convert(varchar,getdate(), 102)

Can you execute manually this query in your asemon database with isql and see if it produces overflow

If yes, try to find if you have abnormal values for IOTime, Reads or Writes in your ASESERVER_DevIO table
The query tries to convert them to numeric(20,0) which is huge but may not be sufficient in some cases

Regards
JP

[Mark Bersales]

Hi JP,
The above query returned values without an error. I tried to review the other queries related to the REPSERVER and found the below causing the arithmetic overflow:

declare @startts datetime
select @startts=isnull(dateadd(dd, 1, (select convert(varchar, max(dt),102) from REPSERVER_Trends where TrendID=16) ),'1900/01/01')
select 16, dt=dateadd(hh,datepart(hh, Timestamp), convert(datetime,convert(varchar,Timestamp,102)) ), Value=avg(val) from (select Timestamp, val=sum(1.*counter_total/1024)/ avg(Interval)
from REPSERVER_RSStats
where Timestamp >= @startts
and Timestamp < convert(varchar,getdate(), 102)
and counter_id=6004 group by Timestamp) lvl0tbl
group by dateadd(hh,datepart(hh, Timestamp), convert(datetime,convert(varchar,Timestamp,102)) )

[Jean-Paul Martin]

Yes I just see that the error was on the REPSERVER purge threadnot ASESERVER

I think the "avg(val) " produce the overflow

You can replace it by "avg(1.*val) "

If this solve the query, you can change it in the file RSTrends.xml :

if not exists (select * from ?SERVERNAME?_TrendsCfg where TrendID=16 and version='3.0')
begin
  delete ?SERVERNAME?_TrendsCfg where TrendID=16
  insert into ?SERVERNAME?_TrendsCfg (TrendID, grpname,SQL, description, version, aggtable, aggrestrict, agg_expr_lvl0, agg_grpby_lvl0, agg_expr, aggfunction, granularity)
  values (16, 'SQM','', 'SQM AvgWrite Kb/s', '3.0', 'RSStats', 'and counter_id=6004', 'val=sum(1.*counter_total/1024)/ avg(Interval)', 'Timestamp', 'avg(1.*val)', 'AVG', 'H')
end

JP

[Mark Bersales]

Thanks JP,
I got an arithmetic overflow still but not after updating avg(Interval) to avg(1.*Interval) as well. Is it correct to update the same?

[Jean-Paul Martin]

Yes you can do this modification and I'll set that in the next version.
Can you check if you have strange values in the Interval column ?
I never saw this problem on many sites where asemon is installed
Nevertheless I'll port the modificatio on all aggregates
Thanks a lot
JP

[Mark Bersales]

Hi JP,
Sorry to reply just now. I tried to apply the changes on the RSTrends.xml but i can see the same error in the debug logs. It is still using the old query(without the 1.* notation. I have stopped and started the asemon logger though. Any idea why is this so?
Regards,
Mark

[Jean-Paul Martin]

Hi Mark
before restarting asemon_logger for your RS, delete rows in the table XXXXX_TrendsCfg
At the first restart of asemon_logger, asemon will re-insert rows in this table , including your modification
Regards
JP

[Mark Bersales]

Thanks a lot JP! I don't see the errors now in the log. Now that it's probably fixed, would i be able to see a significant decrease in the database size of my archive db? Reason i asked is because purging was not working for a long time and our archive db grew to a very large size. The oldest data i can see on REPSERVER_Trends is Nov 2020(see below)

1> select top 1 * from REPSERVER_Trends order by dt asc
2> go
TrendID dt Value

---

   1             Nov 14 2020 12:00AM                        NULL

Our purging setting is only 90 days (xml setting)
<purgearchive daystokeep="90" deletesleep="100"></purgearchive>

If i need to purge manually, can i just delete data on REPSERVER_Trends?

Regards,
Mark

[Jean-Paul Martin]

Mark,
trend data is never purged, and does not use a lot of space
Keep this data, is is interesting to see the trend of some indicators on a long timeframe

Regards
Jp

[Mark Bersales]

Thanks for the clarification JP! Really appreciate your prompt response. How would I know that the data is being purged and what is the schedule of the purging? Any tables i need to check? I need to do capacity management to make sure i have enough space. Right now I have around 500+GB of data usage on one of my archive databases.

[Jean-Paul Martin]

Hi Mark,
most of tables are purged automatically (exept trends and some other very small tables) depending on the "daystokeep" param
If set to 90 (the default) it keeps 90 days of data. Often this param is too high and customers lower it to 30

Monitored data volumes depends on many factors, particularly on activity of your monitored server : number of active connections, number of objects, number of captured queries and plans...
So, decrease this paramter if your database fills too fast

You can set this param at collector level. For example to keep only 10 days of data in the SysMon table :

     <md daysToKeep="10" > SysMon.xml     </md>

(generaly XXXXX_SysMon table is the largest)

Hope this answer your questions
Regards
JP

[Mark Bersales]

Thanks so much JP! Really appreciate you help on this!