database (MSQL 2017) is growing too much in AWA 12.2

[marc broussard]

Hello

We have AWA 12.2 on windows server 2016 and MSQL 2017.
in production we have more or less 400 JOBP with a few insides.
Some are cycling (each 2 minutes ...).

Our database is growing each day of nearly 1 giga.

I would like :
1/ to understand better why : but no idea to do it;
2/ to limit the size fo the database: do you think that to keep all the runs of JOBP and JOBS can explain ?
if so, how to delete the oldest runs (more than 1 year for a start !) in databse ?

thanks for your help

Marc Broussard
ENGIE INEO

[Timothy Yanosko]

Hi
 How often are you running the recommended Automic database maintenance processes (Archive, Reorg, and Unload)?

[marc broussard]

thanks for answering
We do it each night
I did not see any error !

[marc broussard]

the 3 biggest TABLES are:

RT - report content

AH - archive header

AV - object values (archived)
table     "space used" 
RT         264472048 kb
AH         94785816   kb
AV         53208080   kb

[Timothy Yanosko]

if so, how to delete the oldest runs (more than 1 year for a start !) in database ?
The (hopefully) attached whitepaper shows how to configure the db maint workflow.  Using this method you can configure the tables to maintain the days (or runs).  

The GUI interfaces are in the utility/bin directory
ucybdbarg.exe (Archive GUI)
ucybdbreg.exe (Reorg GUI)
ucybdbung.exe (Unload GUI)


Good Luck!

Note this was from v9 i believe, but I doubt much has changed.  Always refer to current doc.

Attachment(s)

WhitePaper-Database Maintenance.pdf   407 KB 1 version

[marc broussard]

Thanks
Usefull

I managed to find that we had it in our system but with : 

auto_Reorg=781
in the ucybdbre.ini

I put 100 days and I had a good result and a question:

the good result :
before 



after :


really lower , good !

but I have stiil things older the 100 days in AH table, I do not know why ?

result of :

select

OH_NAME as 'object name', OH_IDNR, OH_Client as 'Client', AH_Status,

OH_Otype as 'object type'

, dateadd(hour, datediff(hour, getutcdate(), getdate()), ah_timestamp1) as Activation_time

, dateadd(hour, datediff(hour, getutcdate(), getdate()), ah_timestamp2) as start_time

     , dateadd(hour, datediff(hour, getutcdate(), getdate()), ah_timestamp4) as End_time​

        from OH, AH

Where OH_IDNR = AH_OH_Idnr and

oh_client = <number of a client> and

OH_NAME = '<name of a job>'

order by ah_timestamp1

can you help ?

thanks

[marc broussard]

one more thing, in the log for the utiliy job of ucybdbre

i have
Program 'ucybdbre' version '12.3.5+build.1612553994992' started, start parameter: '-V -B -Sxxxx
....
...
20210505/145815.993 - Messages (MELD): read: '781' days ('2019.03.16') unread: '781' days ('2019.03.16')
20210505/145815.993 - Statistics (AH): '100' days ('2021.01.25') Keep last 1000 Statistical records per object
20210505/145815.993 - Reports (RH/RT): '100' days ('2021.01.25') Keep last 1000 Reports per object
20210505/145815.993 - Version Management: '781' days ('2019.03.16') Keep last 50 VC per object

not 100 (but 781 sometimes) every where , strange, I do not know why
any idea ?
thanks

[Timothy Yanosko]

Looks like your keeping 100 days and the last 1000 runs

DB.Reorg allows you to reorganize records older than x days AND keep the last n records.

Statistics (AH): '100' days ('2021.01.25') Keep last 1000 Statistical records per object

[marc broussard]

I managed to find that we can use the graphical tool and there we find more options
ex: 

ucybdbreg.exe

It looks better, but still working on it

Do you know if AWA has down performance if we are archiving/reorg/unlodaing  the datas ?

[Timothy Yanosko]

Yes, I have experienced a performance impact running the database maintenence processes.  In our case it was slight, but noticeable.

[Thierry Tarrason]

Hi Marc,
If you don't want to archive, you can use
no_archive_check = 0
in ucybdbre.ini and just skip the archiving process.

One reason for having 'old' jobs in AH is if they are not 'Deactivated' in the activity window.

------------------------------
Best regards,
Thierry

Banque de Luxembourg
------------------------------

[Frank Muffke]

Hi

just a hint, on our MSSQL Databases for UC4 sandboxes we use the DB - recovery model = simple that keeps the DB from growing that much.
Perhaps its something that could help you.

For details pls refer to MSSQL DB documentation...

cheers, Wolfgang

------------------------------
Support Info:
if you are using one of the latest version of UC4 / AWA / One Automation please get in contact with Support to open a ticket.
Otherwise update/upgrade your system and check if the problem still exists.
------------------------------