CA Service Management

Expand all | Collapse all

Search way to shrink database table "event_log" faster

  • 1.  Search way to shrink database table "event_log" faster

    Posted 05-09-2018 08:07 AM

    Hi all,

     

    I know that I can use a deletion rule in the SDM to specify the event log myself and thus delete logs from the database. This I have applied but strangely enough it takes a very long time.
    Currently I see that my MSSQL table "event_log" is about 4.9 GB in size. Is this size normal? That seems very big to me.
    Anyway, I wanted to reduce this table with the delete rule. Unfortunately, after a runtime of one day he has deleted only 400MB from this table and the rule is still running.
    We want to reduce the size of this database or this very large table again, because this is only our test environment.

    Is there an easy and quick way to reduce the size of this table except for the deletion rules shown here in the web interface itself? The arcpur.log has actually a size about 400MB.

     

    Many greetings,
    Tobias



  • 2.  Re: Search way to shrink database table "event_log" faster

    Broadcom Employee
    Posted 05-09-2018 08:26 AM

    Hello Tobias,


    What you can try to do experimentally, since you mentioned this is a test environment:

     

    • Take a snapshot/backup of your environment.  At minimum, your MDB database should be subject to backup.
    • Stop SDM Services entirely to prevent any further writes to the MDB database on the event log
    • Perform a SQL Server manual deletion of the table's contents.  

     

    One thing I'd be curious about is how much activity is being written to the log and the overall origin of the log itself.  At any point, I'd take a row count of the table and after an hour, take a second row count, to see how many entries are being written on average.  I'd also try and determine where did the table originate from, if it was a copy from production, did any new changes get introduced, basically some way to explain why the table is so big in the first place.  

     

    My biggest concern is if the same table in a production install may be experiencing a similar size issue, especially if the production instance has some sort of relation to the test instance (it was the original source of the test install's DB, it has custom changes first deployed in test environment)



  • 3.  Re: Search way to shrink database table "event_log" faster

    Broadcom Employee
    Posted 05-09-2018 09:15 AM

    Tobias, you can consider incremental archive and purge for the "event log" object. This can be done by create a new archive and rule from Administration->archive and purge->archive and purge rules and create a new rule for "event log" object. Incremental archive purge means you do certain period at a time...for example, you have 8 years data the first run will be data at least 7 years old, second run for 6 years old and so on. Thanks _Chi