Automic Workload Automation

Expand all | Collapse all

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

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

    Posted 14 days ago
    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


  • 2.  RE: database (MSQL 2017) is growing too much in AWA 12.2

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


  • 3.  RE: database (MSQL 2017) is growing too much in AWA 12.2

    Posted 14 days ago
    thanks for answering
    We do it each night
    I did not see any error !


  • 4.  RE: database (MSQL 2017) is growing too much in AWA 12.2

    Posted 14 days ago
    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




  • 5.  RE: database (MSQL 2017) is growing too much in AWA 12.2

    Posted 13 days ago
    Edited by Timothy Yanosko 13 days ago
      |   view attached
    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)



  • 6.  RE: database (MSQL 2017) is growing too much in AWA 12.2

    Posted 12 days ago
    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




  • 7.  RE: database (MSQL 2017) is growing too much in AWA 12.2

    Posted 12 days ago
    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



  • 8.  RE: database (MSQL 2017) is growing too much in AWA 12.2

    Posted 12 days ago
    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


  • 9.  RE: database (MSQL 2017) is growing too much in AWA 12.2

    Posted 12 days ago
    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 ?


  • 10.  RE: database (MSQL 2017) is growing too much in AWA 12.2

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


  • 11.  RE: database (MSQL 2017) is growing too much in AWA 12.2

    Posted 11 days ago
    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
    ------------------------------



  • 12.  RE: database (MSQL 2017) is growing too much in AWA 12.2

    Posted 7 days ago
    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.
    ------------------------------