What is software usage history purging?

ADM continuously records which applications are used by managed devices. That record is accumulated in three history tables that grow indefinitely: the daily detail and its two consolidated ones, daily and monthly.

Scrubbing is the process of deleting that history until a certain date, keeping only the most recent information. The procedure dbo.PRC_ASM_CLEAN_SOFT_USE It runs that batch cleanup of up to 5,000 records per table, so that the operation doesn’t crash the database.

⚐ Note: This procedure requires running T-SQL statements on the database. It is intended for the platform administrator or support personnel with access to SQL Server Management Studio.

What is it used for?

• Free up storage space in the database.
• Reduce response times for queries and software usage reports.
• Monitor the sustained growth of history tables.

What information is deleted?

Each cut-off date is independent and deletes everything prior to it. The indicated date and subsequent records are retained.

A shorter retention period is typically assigned to detail than to consolidated ones, because the breakdown takes up much more space and is viewed less frequently.

The procedure does not intervene in the inherited table dbo.ASM_SOFTWARE_USAGE; Its purification is carried out separately, in step 5.

⚠ Warning: Deletion is final. Cleaned information is only recovered by restoring a backup of the database.

What is required before starting?

• ADM version 9.5.43 or higher. Versions 9.5.33 and earlier use a different structure and should not be debugged with this procedure.
• Current support of the database.
• Maintenance window, since the process generates load on the engine.
• Execution permissions on the procedure and elimination on the tables involved.

⚐ Note: Run all the steps in the same query window. Variables are declared only once at startup; if you open a new tab, the engine responds to the message Must declare the scalar variable.

How do I run debugging?

1. Confirm the database version. If it is 9.5.33 or earlier, do not go ahead and refer to the final considerations.

SELECT * FROM dbo.VERSION_DATABASE;

2. Define the cut-off dates and see how many records are eligible. This check is read-only and does not modify information.

⚐ Note: The cut-off dates are adjusted as required, in the format ‘YYYY-MM-DD’. Example: ‘2026-01-01’.

SET NOCOUNT ON;

-- Configuración: fechas de corte (ajustar según requerimiento)
DECLARE @i_detail date = 'AAAA-MM-DD'; -- ASM_SOFTWARE_USAGE_DETAIL
DECLARE @i_day date = 'AAAA-MM-DD'; -- ASM_SOFTWARE_USAGE_DAY
DECLARE @i_month date = 'AAAA-MM-DD'; -- ASM_SOFTWARE_USAGE_MONTH

DECLARE @detail_key int = dbo.FUN_ADW_DATE_KEY(@i_detail);
DECLARE @day_key int = dbo.FUN_ADW_DATE_KEY(@i_day);
DECLARE @month_key int = dbo.FUN_ADW_DATE_KEY(@i_month);

-- Verificación previa (solo lectura)
SELECT 'ASM_SOFTWARE_USAGE_DETAIL' AS table_name, COUNT_BIG(*) AS eligible_rows
FROM dbo.ASM_SOFTWARE_USAGE_DETAIL
WHERE date_id < @detail_key;

SELECT 'ASM_SOFTWARE_USAGE_DAY' AS table_name, COUNT_BIG(*) AS eligible_rows
FROM dbo.ASM_SOFTWARE_USAGE_DAY
WHERE date_id < @day_key;

SELECT 'ASM_SOFTWARE_USAGE_MONTH' AS table_name, COUNT_BIG(*) AS eligible_rows
FROM dbo.ASM_SOFTWARE_USAGE_MONTH AS su
WHERE EXISTS (
    SELECT 1
    FROM dbo.ADW_DIM_DATE AS dd
    WHERE dd.[year] = su.[year]
      AND dd.[month] = su.[month]
      AND dd.id < @month_key
);

Example: To retain the detail of the last year and the consolidated ones of the last three, assign @i_detail = ‘2026-01-01’, @i_day = ‘2024-01-01’ and @i_month = ‘2024-01-01’.

3. Run the debug.

EXEC dbo.PRC_ASM_CLEAN_SOFT_USE
    @i_detail = @i_detail,
    @i_day = @i_day,
    @i_month = @i_month;

4. Validate the result by repeating the count from step 2. All three queries should return zero. Also see the preserved ranges.

-- Repetir el conteo previo; se espera 0 en las tres tablas
SELECT 'ASM_SOFTWARE_USAGE_DETAIL' AS table_name, COUNT_BIG(*) AS eligible_rows
FROM dbo.ASM_SOFTWARE_USAGE_DETAIL
WHERE date_id < @detail_key;

SELECT 'ASM_SOFTWARE_USAGE_DAY' AS table_name, COUNT_BIG(*) AS eligible_rows
FROM dbo.ASM_SOFTWARE_USAGE_DAY
WHERE date_id < @day_key;

SELECT 'ASM_SOFTWARE_USAGE_MONTH' AS table_name, COUNT_BIG(*) AS eligible_rows
FROM dbo.ASM_SOFTWARE_USAGE_MONTH AS su
WHERE EXISTS (
    SELECT 1
    FROM dbo.ADW_DIM_DATE AS dd
    WHERE dd.[year] = su.[year]
      AND dd.[month] = su.[month]
      AND dd.id < @month_key
);

-- Rangos conservados, para referencia
SELECT MIN(date_id) AS min_date_id, MAX(date_id) AS max_date_id, COUNT_BIG(*) AS total_rows
FROM dbo.ASM_SOFTWARE_USAGE_DETAIL;

SELECT MIN(date_id) AS min_date_id, MAX(date_id) AS max_date_id, COUNT_BIG(*) AS total_rows
FROM dbo.ASM_SOFTWARE_USAGE_DAY;

5. Debug the legacy table dbo.ASM_SOFTWARE_USAGE, only if the database was updated from version 9.5.33 or earlier. This block deletes all records, with no cut-off date, also in batches of 5,000.

⚠ Warning: Perform this step only after confirming in step 1 that the base is running on version 9.5.43 or higher.

-- Verificación previa (solo lectura)
SELECT COUNT_BIG(*) AS total_rows FROM dbo.ASM_SOFTWARE_USAGE;

-- Eliminación por lotes de 5.000 registros (mismo patrón que el PRC documentado)
WHILE 1 = 1
BEGIN
    DELETE TOP (5000) FROM dbo.ASM_SOFTWARE_USAGE;
    IF @@ROWCOUNT = 0 BREAK;
END;

-- Validación posterior (se espera 0)
SELECT COUNT_BIG(*) AS total_rows FROM dbo.ASM_SOFTWARE_USAGE;

What does the message “(0 rows affected)” mean?

This message indicates that there are no records prior to the specified date, not that the procedure failed. This is the expected result when the debug has already been run previously, or when the cut-off dates are older than the stored history.

Similarly, the “(1 row affected)” warnings interleaved correspond to internal variable assignments and do not represent deletions.

To confirm the behavior, see the count in step 2: If you return zero before running the debug, there is no eligible information.

What happens in databases updated from previous versions?

Databases updated from version 9.5.33 or earlier retain history in the dbo.ASM_SOFTWARE_USAGE, which the current procedure no longer uses or debugs. That content is immobilized and only takes up space.

As it is a deprecated version, these records are deleted directly, without migrating them, through step 5 of the procedure.

What considerations apply to versions 9.5.33 and earlier?

These versions do not use the current detail table: the information is based on ASM_SOFTWARE_USAGE, and the consolidated ones are calculated on AFW_DIM_DATE instead of ADW_DIM_DATE. In addition, they have a known defect in the control of the disposal cycle by @@ROWCOUNT, which can prevent daily and monthly cleanings.

In these cases, it is recommended that you update the platform before debugging, rather than replacing the procedure.