Skip to content

How to maintain my database

Introduction

Event information

This table grows with unused information.

JobLog

This table grows, so we need to clean job instances.

Initial conditions

Initial Conditions should be maintained separately (see ManageInitialConditions job task).

Analysis

MIKE WORKBENCH comes with a Database Usage analyser. This is accessible through the Settings menu.

This opens a table showing the size of all tables in the current database workspace.
The biggest tables are shown on top.

Change Log

Change Logs are associated to all types of data (Feature class, Timeseries, etc.).
Everytime a data object is edited, a change log is written.
This is visible in the Change Log Entries window.

Saving change logs can be stoped by editing specific configuration files in the bin folder. This is done by setting the property ChangeLogEntryOnSave to False. This is available for the following files:

  • DHI.Solutions.SpreadsheetManager.Config
  • DHI.Solutions.TimeseriesManager.Config

    If the saving change logs is to be disable for all managers, then it is possible to delete the following entries from the runtime.config file.

  • DHI.Solutions.MetadataManager.Tools.ChangeLogQueryTool.ChangeLogQueryTool
  • DHI.Solutions.MetadataManager.Business.ChangeLogModule
  • DHI.Solutions.MetadataManager.Business.ChangeLogEntry

    Deleting existing change logs can be done with the dedicated task in the Job Manager.

    Timeseries

    Timeseries can be analysed to identify the ones taking the most space.

    Timeseries can be cleaned up automatically with Job tasks:

    Blobs

    Blobs are binary files that can come from many different managers. For example:

  • Scenario manager stores models and initial conditions as BLOBs.
  • Timeseries manager can stores timeseries values as BLOBs.
  • All documents in Document managers are stored as BLOBs.
  • All spreadsheets in Spreadsheet Manager are stored as BLOBs.

    Running the analysis shows the types of BLOB taking the most space.

    It is important to understand that sometime the reference can be lost between the original data object (e.g. timeseries) and the entries in the blob table (time series values). Those entries are called orphans and should be cleaned up. Please read the chapter One-off clean up below for more information.

    GIS Data

    All Feature Class and Raster stored in the GIS manager are saved in a specific table in the database. Those tables' names start with "fc_". In order to know what type each entry starting with fc_ refers to, you should look at the column Entity Name.

    Automatic clean up

    In order to ensure real time systems do not grow in size indefinitely, it is important to set up some automatic cleaning routines. Depending on the implementation, it is possible to set up Job tasks or Workflow activities.

    Job tasks

    Several Job tasks are available in the Maintenance group. More information can be found in the Job Manager section.

    Workflow activities

    One-off clean up

    Clean Orphan Blobs

    A stand alone executable called CleanOrphanBlobs.exe is located in the bin folder.

    This utility analyses and cleans-up orphan blobs in the blobs table.

    The driver behind the development of this utility is the updated version of the Delete Simulations job task for MO 2017.4. It incorrectly assumed that BLOB rows in table the blob table would automatically be removed when deleting the corresponding simulation. This goes for example for BLOB timeseries belonging to a scenarios. The result is that, potentially, a significant amount of data (rows) has been left in the blob table when using this job task.

    However, blobs might potentially become orphans via other means.

    It is advised to make a backup before running the CleanOrphanBlobs utility.

    Steps Description
    Starting this executable will open the following window.
    The connection to the database needs to be defined
    Fill in the the connection information to the database server
    Select the database
    Define path to the output log file
    Select type of Blob to investigate.

    Note: Checking “UNKNOWN” will analyse all blob types.

    This will add all types in the orhpan blob type table

    If no blob types has been selected, only the initial blob id count is performed (see Blob count view below).

    The Orphan Blob Type table will be left empty.
    Ticking Delete Orphan blobs will delete the orphan while they are identified (and only the blob types selected).
    Execute will run the analysis in a separate process
    Blob count view contains orphan blob type count.

    The count is updated during execution, so that the user can see the progress.

    The Blob count view gives the initial Blob id count.
    Orphan blob count contains the total count of orphan blobs.
    Total blob count give the count of all blob entries in the blob table.
    The status line will show the number of orphan blobs analysed.
    Eventually, the orphan blob type table will be filled and provide insights into the types of orphans generated.

    If the problem is recuring, then the configurer should investigate how blob of certain types are being generated by the system.
    The analyses process can be stopped with the terminate button.

    Note: This does not close the application

    Database management

    Vacum

    Backup