How the Garbage Collector works

As new snowpacks are processed, the Data Processor module performs direct INSERT operations into the applicable tables rather than determining whether it needs to perform an INSERT or UPDATE. This significantly speeds up file processing. The Garbage Collector then removes the extra data.

The Garbage Collector runs according to the Interval setting for the Garbage Collector module in the snowserver.config file. The default interval is two hours. The next run begins two hours after the previous run completes, but the Garbage Collector does not run while the Data Update Job (DUJ) is running.

The Garbage Collector performs the following 13 steps:

1. Updates statistics on all tables using sys.sp_updatestats to make query plans accurate, if UpdateStatistics is enabled.
2. Cleans up metering tables by running inv.CleanUpMeteringTsForDeletion.
3. Removes users that have been marked for deletion.
4. Removes clients that have been marked for deletion.
5. Removes old identities that are more than DaysToKeepAssignedIdsWithoutAnyDataDate days old.
6. Removes all entries lower than the highest sequence number in tables governed by sequence numbers so that the latest information is retained.
7. Removes entries from inv.ChangeSequenceNumber that are older than DaysOfCumulativeTableHistoryToKeep days.
8. Removes rows from inv.DataLoginUser, inv.DataMetering, inv.DataMetering_History, inv.DataMeteringConcurrency, inv.DataMeteringConcurrency_History, inv.DataWebAppMetering, inv.DataLparInfo, inv.DataWparInfo, and inv.DataProcessorExtended that are older than DaysOfCumulativeTableHistoryToKeep days or, for some cases, 1,200 days (DataLparInfo, DataWparInfo, and DataProcessorExtended).
9. Removes data applications that are not referenced by any of the following tables: inv.DataSoftware, inv.DataSoftwareProduct, inv.DataGeneratedSoftware, inv.DataOperatingSystem, inv.DataVirtualSoftwarePackages, inv.DataCustomRegKey, inv.DataWebApplication, inv.DataMetering, inv.DataMetering_History, inv.DataMeteringConcurrency, and inv.DataMeteringConcurrency_History.
10. Removes cloud metering rows that are older than DaysOfCumulativeTableHistoryToKeep days.
11. Removes rows from inv.ClientStorageData that are older than the value defined in the DaysToRetain column in the table.
12. Removes completed command jobs.
13. Removes rows from inv.ServerMetricData that are older than DaysOfMetricHistoryToKeep.

Errors that occur during any of these steps should appear in the Snow Inventory Server logs located at:

C:\ProgramData\SnowSoftware\Inventory\Server

These logs typically have a maximum size of 2 MB. Because the Garbage Collector runs only when the Inventory Service is restarted or at its scheduled interval, the logs covering the Garbage Collector run may no longer be available when troubleshooting begins.

Check whether clients are being deleted

SELECT 'Total ClientIds in deletion table', Count(1) as 'RowCount'
FROM SnowInventory.inv.ClientsForDeletion cfd

UNION

SELECT 'ClientIds in table that are not in DataClient', Count(1) as 'RowCount'
FROM SnowInventory.inv.ClientsForDeletion cfd
LEFT JOIN SnowInventory.inv.DataClientView2 dc on cfd.clientid = dc.clientid
WHERE dc.clientid is null

UNION

SELECT 'ClientIds added to table prior to today', Count(1) as 'RowCount' FROM SnowInventory.inv.ClientsForDeletion cfd
LEFT JOIN SnowLicenseManager.inv.tblComputerInvSlmMap map on cfd.ClientId = map.ClientId
WHERE map.DeletedDate < CONVERT(DATE, GETDATE())

Expected results

If the Garbage Collector has run successfully during the current calendar day, the RowCount value should be zero for the following rows:

• ClientIds added to table prior to today
• ClientIds in table that are not in DataClient

The Total ClientIds in deletion table row may have a value greater than zero if ClientIds were sent to the table from the Inventory Service on the SLM server after the most recent Garbage Collector run.

Check whether users are being deleted

SELECT 'Total Users in table', Count(1) as 'RowCount'
FROM SnowInventory.inv.UsersForDeletion cfd

UNION

SELECT 'ClientIds in table that are not in DataUserAccount', Count(1) as 'RowCount'
FROM SnowInventory.inv.UsersForDeletion cfd
LEFT JOIN SnowInventory.inv.DataUserAccountView2 dc on cfd.UserId = dc.UserId
WHERE dc.UserId is null

UNION

SELECT 'Users added to table prior to today', Count(1) as 'RowCount'
FROM SnowInventory.inv.UsersForDeletion cfd
LEFT JOIN SnowLicenseManager.inv.tblUserInvSlmMap map on cfd.UserId = map.SLM_UserID 
WHERE map.DeletedDate < CONVERT(DATE, GETDATE())

Expected results

If the Garbage Collector has run successfully during the current calendar day, the RowCount value should be zero for the following rows:

• Users added to table prior to today
• Users in table that are not in DataUserAccount

The Total Users in table row may have a value greater than zero if users were sent to the table from the Inventory Service on the SLM server after the most recent Garbage Collector run.

Check whether extra sequence numbers are being removed

This query checks only DataSoftware, which is typically the largest of the tables being cleaned. The other two tables that may experience similar issues are DataNTService and DataDeviceDriver.

IF OBJECT_ID('tempdb..#SeqNbr') IS NOT NULL
    DROP TABLE #Files
IF OBJECT_ID('tempdb..#SeqNbr') IS NOT NULL
    DROP TABLE #Files

SELECT Clientid, Count(1) as 'FilesProcessedToday'
Into #Files
FROM SnowInventory.Inv.ChangeSequenceNumber
WHERE clientid is not null and CONVERT(DATE, ProcessingDate) = CONVERT(DATE, GETDATE())
GROUP BY ClientId

SELECT ClientId, Count(Distinct SequenceNumber) as 'SeqNbrs'
INTO #SeqNbr
FROM SnowInventory.inv.DataSoftware
GROUP BY Clientid

SELECT #SeqNbr.ClientId, #Files.FilesProcessedToday as 'Files Processed Today', #SeqNbr.SeqNbrs as 'Count of Seq Nbrs'
FROM #SeqNbr
LEFT JOIN #Files on #SeqNbr.ClientId = #Files.ClientId
WHERE #SeqNbr.SeqNbrs > #Files.FilesProcessedToday
ORDER BY #SeqNbr.SeqNbrs desc

Expected results

If the Garbage Collector has run successfully during the current calendar day, the query should return a small number of ClientIds, and the Count of Seq Nbrs values should be low.

Deletions from each Data table are performed in batches of 2 million rows, with a maximum of 500 batches per table. Depending on the number of rows that need to be deleted, the module may require multiple runs to completely clean a table.

If the query returns a large number of ClientIds, the Garbage Collector module may be timing out and unable to clean larger tables such as DataSoftware.

Check whether the CloudMeteringIntervalHits table is being cleaned

IF OBJECT_ID('tempdb..#MeteringIds') IS NOT NULL
    DROP TABLE #MeteringIds

IF OBJECT_ID('tempdb..#IntervalIds') IS NOT NULL
    DROP TABLE #IntervalIds

SELECT Id
INTO #MeteringIds
FROM SnowInventory.inv.CloudMeteringData cmd
WHERE cmd.MeteringDate < Getdate() - 365 -- change this to the value in the "DaysOfCumulativeTableHistoryToKeep" setting if it has been overridden in the snowserver.config file. Otherwise, leave the default of 365 days.

SELECT cmi.Id
INTO #IntervalIds
FROM SnowInventory.inv.CloudMeteringIntervals cmi
INNER JOIN #MeteringIds mi ON cmi.MeteringId = mi.Id

SELECT 'CloudMeteringIntervalHits', count(1) as RowsToBeDeleted 
FROM SnowInventory.inv.CloudMeteringIntervalHits cmih 
INNER JOIN #IntervalIds ii ON cmih.IntervalId = ii.id

Expected results

The Garbage Collector code for cleaning the CloudMeteringIntervalHits table was modified in SI 6.8.4 so that deletions are performed in batches of 2 million rows instead of as a single deletion.

Customers running earlier versions may see a large number of rows that need to be deleted from this table. Snow Support has a script that can be used to clean the table in batches of 2 million rows.

Check whether history tables are being cleaned

This query checks only the DataMetering_History table because it is one of the larger tables cleaned by the Garbage Collector.

SELECT Count(1) as 'Rows to be Removed'
FROM SnowInventory.inv.DataMetering_History
WHERE [Date] < Getdate() - 365 --change this to the value in "DaysOfCumulativeTableHistoryToKeep" setting in snowserver.config if the default has been overridden. Otherwise, leave the default of 365 days.

Expected results

The Garbage Collector module deletes 2 million rows per batch for a maximum of 500 cycles. Depending on the number of rows that need to be deleted, the module may require multiple runs to completely clean a table.

Troubleshoot Garbage Collector cleanup issues

If the Garbage Collector is not cleaning data as expected, review the following possible causes.

SnowInventory indexes are highly fragmented

Make sure Index Maintenance is scheduled and running without errors.

Tables are too large and deletion queries are timing out

Contact Support to run one-time table cleanup scripts and determine why the Garbage Collector is timing out.

High latency on the SnowInventory database

If index fragmentation has already been ruled out, review the SAN storage configuration and confirm that it meets the IOPS requirements documented in Best Practices for Large Environments.

The Garbage Collector module is disabled in snowserver.config

The default value of the IsEnabled property in the GarbageCollectionModule is true. If the snowserver.config file has been modified to override other Garbage Collection module settings, confirm that the module is enabled:

true

Best Practices for Large Environments