Data Update Job execution time increases due to a large dbo.tblSoftware table

Overview of the cleanup process

The cleanup of the dbo.tblSoftware table occurs in three steps:

1. Garbage Collector: The Garbage Collector in Snow Inventory removes applications from the inv.DataApplication table when they do not have any references. No references indicate that the application is no longer being used.
2. UpdateActiveSoftwareChecksums: During the Data Update Job, the inv.UpdateActiveSoftwareChecksums step gathers active software checksums from the inv.DataApplication and dbo.tblMetering tables and stores them in dbo.tblActiveSoftwareChecksums.
3. SLM Cleanup Metering Service: The dbo.tblSoftware table is cleaned by removing inactive rows. The SLM Cleanup Metering Service deletes software rows from dbo.tblSoftware based on the information stored in dbo.tblActiveSoftwareChecksums.

Step 1: Garbage Collector

Product improvements help maintain the dbo.tblSoftware table going forward. However, they may not fully resolve a backlog of rows that accumulated previously. A backlog can exist in both inv.DataApplication and dbo.tblSoftware.

First, verify that the Garbage Collector can remove applications that are no longer in use from inv.DataApplication.

Review the Snow Inventory logs located at:

C:\ProgramData\SnowSoftware\Inventory\Server

Example log output when garbage collection is working

2022-12-02T11:04:29+01:00;garbage-collection;;;;Starting garbage collection. Interval=2.00 hours | BatchSize=2000000 | Timeout=180 seconds
2022-12-02T11:04:29+01:00;garbage-collection;;;;Gargbage collection done, total time elapsed: 00:00:00.2027658
2022-12-02T11:04:29+01:00;garbage-collection;;;;Steps: MeteringTables: 0.02s, Users: 0.01s, Clients: 0s, Identity: 0.01s, SequenceTables: 0.03s, ChangeSequenceNumbers: 8 in 0.01s, DataTables: 0.01s, DataApplication: 0.08s, CloudMetering: 0.01s, ClientStorageData: 0s, CommandJobs: 4 in 0.01s, ServerMetricData: 0.01s

Example log output when garbage collection is not working

2023-01-19T23:50:31+00:00;garbage-collection;;;;DataApplication table cleanup failed, current chunk remains unprocessed: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

If this timeout message appears in the log, add the Garbage Collector module to the Snowserver.config file located at:

C:\Program Files\Snow Software\Snow Inventory\Server

Add the following module:

<Module typeName="GarbageCollectionModule">
  <Setter propertyName="IsEnabled">true</Setter>
  <Setter propertyName="UpdateStatistics">false</Setter>
  <Setter propertyName="Interval">"02:00:00"</Setter>
  <Setter propertyName="DaysOfCumulativeTableHistoryToKeep">90</Setter>
  <Setter propertyName="DaysOfMetricHistoryToKeep">30</Setter>
  <Setter propertyName="DaysToKeepAssignedIdsWithoutData">7</Setter>
  <Setter propertyName="SqlCommandTimeOut">1800</Setter>
</Module>

The SqlCommandTimeOut value is set to 1800 seconds. This change should prevent the timeout and allow garbage collection to clean the inv.DataApplication table.

In the successful log example, note the amount of time required to clean DataApplication. When this processing time has decreased, you can lower the SqlCommandTimeOut value. Do not set it lower than 180 seconds.

You can also remove the module from the Snowserver.config file to revert to the default settings.

Step 2: UpdateActiveSoftwareChecksums

After Step 1 is complete and verified:

• All checksums in the inv.DataApplication table are in use and need to be applied.
• Applications containing only metering data also need to be applied.

The garbage collector procedure inv.UpdateActiveSoftwareChecksums takes both scenarios into account when populating the dbo.tblActiveSoftwareChecksums table.

Each time this procedure runs, it drops and recreates the dbo.tblActiveSoftwareChecksums table so that it contains fresh data from the most recent DUJ execution.

Step 3: Clean dbo.tblSoftware

The SLM Cleanup Metering Service in Snow License Manager cleans the dbo.tblSoftware table by removing unwanted software rows based on the contents of dbo.tblActiveSoftwareChecksums.

The service includes configurable options that can be used to clean up a backlog of software records.

These settings are located in the appsetting.json file at:

C:\Program Files\Snow Software\Snow License Manager\Services\CleanupMetering

The settings related to cleanup of the dbo.tblSoftware table are:

"SoftwareSettings": {
  "RowsToDelete": "1000000",
  "Schedules": [
    "06:00",
    "12:00",
    "18:00"
  ]

Edit the appsetting.json file to configure the number of rows to delete and the times when cleanup is scheduled.

To help the cleanup complete more quickly, you can increase the RowsToDelete value. However, setting this value too high can result in a timeout. Timeouts are recorded in the service logs located at:

C:\Program Files\Snow Software\Logs\CleanupMetering

To remove a large backlog from dbo.tblSoftware, the recommended approach is to increase the number of scheduled cleanup runs per day according to the needs of the environment.

For example:

"SoftwareSettings": {
  "RowsToDelete": "1000000",
  "Schedules": [
    "00:00",
    "02:00",
    "04:00",
    "06:00",
    "08:00",
    "10:00",
    "12:00",
    "14:00",
    "16:00",
    "18:00",
    "20:00",
    "22:00"
  ]

Before making any changes, compare dbo.tblSoftware with dbo.tblActiveSoftwareChecksums to determine approximately how much data needs to be deleted.

For example, if 40 million rows need to be deleted, you could configure the cleanup to remove approximately 10 million rows per day, clearing the backlog in approximately four days.

After the cleanup is complete

Reducing the backlog in dbo.tblSoftware should result in a corresponding improvement in Data Update Job execution time.

This improvement applies to both a normal DUJ and a Complete Ruleset execution, which can take significantly longer to complete. For additional guidance, see Configure the Complete Ruleset.

After the tables have been cleaned and the Data Update Job is completing successfully, review the configuration changes described above. Reconfigure the settings as appropriate for the environment so that the cleanup processes continue to run regularly and another backlog does not accumulate.