Database Cleanup Utility

A guide to performing database cleanup.

On this page:

• Introduction
• Prerequisites
• Supported database versions
• Cleanup categories and SQL file descriptions
  • OAuth cleanup (cleanup_oauth_*.sql)
  • Audit cleanup (cleanup_audit_*.sql)
  • Board Items and Notifications (cleanup_delete_board_items_dn_wf_notifications_*.sql)
  • Alerts cleanup (cleanup_alerts_*.sql)
  • Usage Statistics (cleanup_usage_*.sql)
  • SLA cleanup (cleanup_sla_*.sql)
  • OS (Operational Store) cleanup (cleanup_os_*.sql)
  • Rollup/Aggregation cleanup (cleanup_rollup_*.sql)
• Configuration parameters
• Execution instructions
  • MySQL
  • Oracle
  • MSSQL (Linux)
  • MSSQL (Windows)
  • Database cleanup execution log format
• Troubleshooting

Introduction

This document provides comprehensive instructions for executing database cleanup operations across MySQL, Oracle, and Microsoft SQL Server environments. The cleanup utility removes stale data from OAuth tokens, audit logs, board items, alerts, usage statistics, SLA records, and system tables based on the configurable retention periods.

Prerequisites

Before you begin, ensure you meet the following prerequisites:

General requirements

Database-specific tools

Supported database versions

Cleanup categories and SQL file descriptions

Provides an overview of cleanup categories and associated SQL file descriptions for removing obsolete and expired data.

OAuth cleanup (cleanup_oauth_*.sql)

Purpose: Removes the expired OAuth 2.0 grant data, access tokens, refresh tokens, and related metadata.

Tables affected:

Retention period: 365 hours (subject to the token expiry configuration).

Audit cleanup (cleanup_audit_*.sql)

Purpose: Purges the old audit trail records to maintain compliance and improve performance.

Tables affected:

Retention period: 168 hours (7 days) in accordance with compliance requirements.

Board Items and Notifications (cleanup_delete_board_items_dn_wf_notifications_*.sql)

Purpose: Removes the stale discussion items, alerts, notifications, and workflow-related data from the Community Manager portal dashboard.

Tables affected:

Retention period:

High-volume systems: 1–24 hours.

Standard deployments: 168 hours (7 days) or as defined by organization requirements.

Alerts cleanup (cleanup_alerts_*.sql)

Purpose: Provides a dedicated cleanup of the system alert tables.

Tables affected:

Retention period: 168 hours or as defined by organization requirements.

Usage Statistics (cleanup_usage_*.sql)

Purpose: Removes the aggregated usage statistics and metrics data.

Tables affected:

Retention period: 720 hours (3–30 days) for trend analysis or as defined by organization requirements.

SLA cleanup (cleanup_sla_*.sql)

Purpose: Purges the outdated Service Level Agreement (SLA) monitoring data and breach records.

Tables affected:

Retention period: 168–720 hours (7–30 days) or as defined by organization requirements.

OS (Operational Store) cleanup (cleanup_os_*.sql)

Purpose: Cleans the Operational Store (OS) tables by removing outdated data to improve overall system performance.

Tables affected:

Retention period: 24–168 hours (1–7 days).

Rollup/Aggregation cleanup (cleanup_rollup_*.sql)

Purpose: Removes the rolled‑up and aggregated data that has been archived or superseded.

Tables affected:

Retention period: 720+ hours (30+ days) or as defined by organization requirements.

Configuration parameters

This section describes the global parameters, per‑file configuration format, and database connection parameters required to configure the utility.

Global parameters (All databases)

Per-file configuration format

Configuration examples (bash):

# Full configuration (path + hours + batch)
/opt/cleanup_oauth.sql|24|1000
# Hours only (uses default batch)
/opt/cleanup_audit.sql|48
# Path only (uses both defaults)
/opt/cleanup_alerts.sql

Database connection parameters

MySQL

Oracle

MSSQL

Execution instructions

Before you begin, ensure that all prerequisites are met and that the cleanup script is available.

MySQL

Use the MySQL cleanup script to remove outdated data from selected database tables based on configured retention periods and batch sizes. To view sample database cleanup scripts, download the zip archive: db_cleanup_scripts.zip.

Step 1: Setup the environment

1. Verify the active MySQL installation by running the following command:
   Copy

   which mysql
   # Expected output: /usr/local/mysql/bin/mysql

2. Enable execution of the cleanup script by running the following command:
   Copy

   chmod +x cleanup_mysql.sh

Step 2: Configure the script

1. Specify the required database connection details.
   Copy

   # Database Credentials (REQUIRED)
   DB_USER="your_username"
   DB_PASS="your_password"
   DB_NAME="your_db"
   DB_HOST="mysql.host"
   DB_PORT=3306

2. Specify the SQL files to execute by commenting or uncommenting the files that need to be executed. The following SQL files are executed sequentially.
   Copy

   declare -a SQLFILES_WITH_CONFIG=(
   "$BASE_DIR/cleanup_oauth_mysql.sql|1|8"
   "$BASE_DIR/cleanup_audit_mysql.sql|2|7"
   "$BASE_DIR/cleanup_delete_board_items_dn_wf_notifications_mysql.sql|3|6"
   "$BASE_DIR/cleanup_alerts_mysql.sql|4|5"
   "$BASE_DIR/cleanup_usage_mysql.sql|5|4"
   "$BASE_DIR/cleanup_sla_mysql.sql|6|3"
   "$BASE_DIR/cleanup_os_mysql.sql|7|2"
   "$BASE_DIR/cleanup_rollup_mysql.sql|8|1"
   )

Step 3: Execute the cleanup script

Run the script by using the following command:

./cleanup_mysql.sh

Oracle

Use the Oracle cleanup script to remove outdated data from selected database tables based on configured retention periods and batch sizes. To view sample database cleanup scripts, download the zip archive: db_cleanup_scripts.zip.

Step 1: Setup the environment

1. Verify that the Oracle client tools are installed and accessible by running the following command:
   Copy

   sqlplus -v

2. Enable execution of the cleanup script by running the following command:
   Copy

   chmod +x cleanup_oracle.sh

Step 2: Configure the script

1. Specify the required database connection details.
   Copy

   DB_USER="schema_user"
   DB_PASS="password"
   DB_NAME="ORCL"
   DB_HOST="oracle.host"

2. Specify the SQL files to execute by commenting or uncommenting the files that need to be executed. The following SQL files are executed sequentially.
   Copy

   declare -a SQLFILES_WITH_CONFIG=(
       "$BASE_DIR/cleanup_oauth_oracle.sql"
       "$BASE_DIR/cleanup_audit_oracle.sql|2|7"
       "$BASE_DIR/cleanup_delete_board_items_dn_wf_notifications_oracle.sql|3|6"
       "$BASE_DIR/cleanup_alerts_oracle.sql|4|5"
       "$BASE_DIR/cleanup_usage_oracle.sql|5|4"
       "$BASE_DIR/cleanup_sla_oracle.sql|6|3"
       "$BASE_DIR/cleanup_os_oracle.sql|7|2"
       "$BASE_DIR/cleanup_rollup_oracle.sql|8|1"
   )

Step 3: Execute the cleanup script

Run the script by using the following command:

./cleanup_oracle.sh

MSSQL (Linux)

Use the MSSQL cleanup script to remove outdated data from selected database tables based on configured retention periods and batch sizes. To view sample database cleanup scripts, download the zip archive: db_cleanup_scripts.zip.

Step 1: Setup the environment

Install the required Microsoft SQL Server client tools by running the following CURL command:

# Install tools
curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo
ACCEPT_EULA=Y yum install -y msodbcsql17 mssql-tools

Step 2: Configure the script

1. Specify the required database connection details.
   Copy

   SERVER="tcp:your-server,1433"
   DATABASE="your_db"
   DB_USER="user"
   DB_PASS="pass"

2. Specify the SQL files to execute by commenting or uncommenting the files that need to be executed. The following SQL files are executed sequentially.
   Copy

   declare -a SQLFILES_WITH_CONFIG=(
       "$BASE_DIR/cleanup_oauth_mssql.sql"
       "$BASE_DIR/cleanup_audit_mssql.sql|2|7"
       "$BASE_DIR/cleanup_delete_board_items_dn_wf_notifications_mssql.sql|3|6"
       "$BASE_DIR/cleanup_alerts_mssql.sql|4|5"
       "$BASE_DIR/cleanup_usage_mssql.sql|5|4"
       "$BASE_DIR/cleanup_sla_mssql.sql|6|3"
       "$BASE_DIR/cleanup_os_mssql.sql|7|2"
       "$BASE_DIR/cleanup_rollup_mssql.sql|8|1"
   )

Step 3: Execute the cleanup script

Run the script by using the following command:

./cleanup_mssql.sh

MSSQL (Windows)

Use the MSSQL cleanup script to remove outdated data from selected database tables based on configured retention periods and batch sizes. To view sample database cleanup scripts, download the zip archive: db_cleanup_scripts.zip.

Step 1: Setup the environment

Verify that the SQL Server command-line tools are installed by running the following command:

sqlcmd -?

Step 2: Configure the script

1. Update the required database connection details.
   Copy

   set "SERVER=tcp:server,1433"
   set "DATABASE=db"
   set "DB_USER=user"
   set "DB_PASS=pass"

2. Specify the SQL files to execute by commenting or uncommenting the files that need to be executed. The following SQL files are executed sequentially.
   Copy

   set "SQLCONFIG0=%BASE_DIR%\cleanup_oauth_mssql.sql|1|8"
   set "SQLCONFIG1=%BASE_DIR%\cleanup_audit_mssql.sql|2|7"
   set "SQLCONFIG2=%BASE_DIR%\cleanup_delete_board_items_dn_wf_notifications_mssql.sql|3|6"
   set "SQLCONFIG3=%BASE_DIR%\cleanup_alerts_mssql.sql|4|5"
   set "SQLCONFIG4=%BASE_DIR%\cleanup_usage_mssql.sql|5|4"
   set "SQLCONFIG5=%BASE_DIR%\cleanup_sla_mssql.sql|6|3"
   set "SQLCONFIG6=%BASE_DIR%\cleanup_os_mssql.sql|7|2"
   set "SQLCONFIG7=%BASE_DIR%\cleanup_rollup_mssql.sql"

Step 3: Execute the cleanup script

Run the script by using the following command:

cleanup_mssql.bat

Database cleanup execution log format

Log files summarize database cleanup activity. Statistics logs (db_cleanup_*_stats.log) show execution details and deletion counts, while SQL logs (db_cleanup_*_sql.log) record the executed statements.

The following is a sample execution log format:

• db_cleanup_*_stats.log: Contains execution statistics, including row counts and timing information.

Cleanup started at Wed Mar  4 09:38:24 MST 2026
Total no. of iterations: 2
Default retention hours: 24
Default rows per batch: 1000
 Per-file configuration:
  File                                                         |  Hours | Batch
  -----------------------------------------------------------------------------

  cleanup_oauth_mysql.sql                                      |     1h |   8       

  cleanup_audit_mysql.sql                                      |     2h |   7      

  cleanup_delete_board_items_dn_wf_notifications_mysql.sql     |     3h |   6       

  cleanup_alerts_mysql.sql                                     |     4h |   5      

  cleanup_usage_mysql.sql                                      |     5h |   4       

  cleanup_sla_mysql.sql                                        |     6h |   3      

  cleanup_os_mysql.sql                                         |     7h |   2       

  cleanup_rollup_mysql.sql                                     |     8h |   1  

============================================================
== ITERATION 1 :: Wed Mar  4 09:38:25 MST 2026
============================================================
--- Running SQL file: cleanup_oauth_mysql.sql (retention: 1h, batch: 8) at Wed Mar  4 09:38:25 MST 2026 ---
Results for cleanup_oauth_mysql.sql:

 OAUTH_GRANT_PROPERTIES                   rows=0        time=2ms

 OAUTH_RO_AUTH_INSTRUCTIONS               rows=0        time=2ms

 OAUTH_GRANT_RO_USERINFO                  rows=0        time=5ms

 OAUTH_TOKENS                             rows=0        time=2ms

 OAUTH_MAC_TOKENS                         rows=0        time=1ms

 OPENID_CONNECT_GRANTS                    rows=0        time=1ms

 OAUTH_RO_USERINFO_EXTN                   rows=0        time=1ms

 OAUTH_JWT_TOKENS                         rows=0        time=1ms

 OAUTH_GRANTS                             rows=0        time=5ms

--- Completed: cleanup_oauth_mysql.sql ---
--- Running SQL file: cleanup_audit_mysql.sql (retention: 2h, batch: 7) at Wed Mar  4 09:38:25 MST 2026 ---
Results for cleanup_audit_mysql.sql:

 PM_AUDITTRAIL                            rows=0        time=2ms

 AM_AUDITTRAIL                            rows=0        time=1ms

--- Completed: cleanup_audit_mysql.sql ---
--- Running SQL file: cleanup_audit_mysql.sql (retention: 2h, batch: 7) at Wed Mar  4 09:38:31 MST 2026 ---
Results for cleanup_audit_mysql.sql:

 PM_AUDITTRAIL                            rows=0        time=2ms

 AM_AUDITTRAIL                            rows=0        time=1ms

--- Completed: cleanup_audit_mysql.sql ---
…………
…………
…………
**************** ITERATION SUMMARY (Iteration 2) ****************
tables_with_deletes=14 total_rows=59 iteration_time=1s
********************************************************************
Max loop limit reached. Stopping cleanup for safety.
Cleanup completed at Wed Mar  4 09:38:37 MST 2026
=========================================

• db_cleanup_*_sql.log: Contains the actual SQL statements executed during the cleanup.

=== New run started at 2026-03-04T09:38:24-07:00 ===

DELETE gp FROM OAUTH_GRANT_PROPERTIES gp JOIN (   SELECT g.GRANTSEQID   FROM OAUTH_GRANTS g   WHERE g.STATUS IN ('Rejected','Error','Expired','ClientDeleted','Revoked','Cancelled')     AND g.UPDATED < TIMESTAMPADD(HOUR, -1, NOW())   ORDER BY g.UPDATED   LIMIT 8) s ON s.GRANTSEQID = gp.GRANTSEQID;

DELETE rai FROM OAUTH_RO_AUTH_INSTRUCTIONS rai JOIN (   SELECT g.GRANTSEQID   FROM OAUTH_GRANTS g   WHERE g.STATUS IN ('Rejected','Error','Expired','ClientDeleted','Revoked','Cancelled')     AND g.UPDATED < TIMESTAMPADD(HOUR, -1, NOW())   ORDER BY g.UPDATED   LIMIT 8) s ON s.GRANTSEQID = rai.GRANTSEQID;

DELETE grui FROM OAUTH_GRANT_RO_USERINFO grui JOIN (   SELECT g.GRANTSEQID   FROM OAUTH_GRANTS g   WHERE g.STATUS IN ('Rejected','Error','Expired','ClientDeleted','Revoked','Cancelled')     AND g.UPDATED < TIMESTAMPADD(HOUR, -1, NOW())   ORDER BY g.UPDATED   LIMIT 8) s ON s.GRANTSEQID = grui.GRANTSEQID;

DELETE ot FROM OAUTH_TOKENS ot JOIN (   SELECT g.GRANTSEQID   FROM OAUTH_GRANTS g   WHERE g.STATUS IN ('Rejected','Error','Expired','ClientDeleted','Revoked','Cancelled')     AND g.UPDATED < TIMESTAMPADD(HOUR, -1, NOW())   ORDER BY g.UPDATED   LIMIT 8) s ON s.GRANTSEQID = ot.GRANTSEQID;

DELETE omt FROM OAUTH_MAC_TOKENS omt JOIN (   SELECT g.GRANTSEQID   FROM OAUTH_GRANTS g   WHERE g.STATUS IN ('Rejected','Error','Expired','ClientDeleted','Revoked','Cancelled')     AND g.UPDATED < TIMESTAMPADD(HOUR, -1, NOW())   ORDER BY g.UPDATED   LIMIT 8) s ON s.GRANTSEQID = omt.GRANTSEQID;

DELETE ocg FROM OPENID_CONNECT_GRANTS ocg JOIN (   SELECT g.GRANTSEQID   FROM OAUTH_GRANTS g   WHERE g.STATUS IN ('Rejected','Error','Expired','ClientDeleted','Revoked','Cancelled')     AND g.UPDATED < TIMESTAMPADD(HOUR, -1, NOW())   ORDER BY g.UPDATED   LIMIT 8) s ON s.GRANTSEQID = ocg.GRANTSEQID;

DELETE ruie FROM OAUTH_RO_USERINFO_EXTN ruie JOIN (   SELECT g.GRANTSEQID   FROM OAUTH_GRANTS g   WHERE g.STATUS IN ('Rejected','Error','Expired','ClientDeleted','Revoked','Cancelled')     AND g.UPDATED < TIMESTAMPADD(HOUR, -1, NOW())   ORDER BY g.UPDATED   LIMIT 8) s ON s.GRANTSEQID = ruie.GRANTSEQID;

DELETE jwt FROM OAUTH_JWT_TOKENS jwt JOIN (   SELECT g.GRANTSEQID   FROM OAUTH_GRANTS g   WHERE g.STATUS IN ('Rejected','Error','Expired','ClientDeleted','Revoked','Cancelled')     AND g.UPDATED < TIMESTAMPADD(HOUR, -1, NOW())   ORDER BY g.UPDATED   LIMIT 8) s ON s.GRANTSEQID = jwt.GRANTSEQID;

DELETE g FROM OAUTH_GRANTS g JOIN (   SELECT GRANTSEQID   FROM OAUTH_GRANTS   WHERE STATUS IN ('Rejected','Error','Expired','ClientDeleted','Revoked','Cancelled')     AND UPDATED < TIMESTAMPADD(HOUR, -1, NOW())   ORDER BY UPDATED   LIMIT 8) s ON s.GRANTSEQID = g.GRANTSEQID;

DELETE FROM PM_AUDITTRAIL WHERE DTSCREATE < TIMESTAMPADD(HOUR, -2, NOW()) LIMIT 7;

DELETE FROM AM_AUDITTRAIL WHERE CREATEDTS < TIMESTAMPADD(HOUR, -2, NOW()) LIMIT 7;
…………
…………
…………
…………

Troubleshooting

Use this section to identify common errors, determine the underlying cause, and resolve the issue.

Common issues and solutions

Log analysis

After running the database cleanup utility, review the generated log files to identify errors, warnings, or unexpected behavior. Log analysis helps you quickly verify whether the operation completed successfully or requires follow‑up investigation.

Use standard command‑line tools to scan the log files for error messages. Most cleanup utilities write status information to plain‑text logs, making them easy to search.

The following commands search a database cleanup log file for lines that contain the word error. They help you quickly identify whether the cleanup utility encountered problems during execution. Although the examples are grouped by database type, the behavior is identical, only the log file name changes.

# MySQL/Linux
grep -i "error" db_cleanup_mysql_stats.log
# Oracle/Linux
grep -i "error" db_cleanup_oracle_stats.log
# MSSQL/Linux
grep -i "error" db_cleanup_mssql_stats.log
# Windows (PowerShell)
Select-String -Path "db_cleanup_mssql_stats.log" -Pattern "error"

Performance tuning

Performance tuning helps you balance cleanup speed, database load, and system stability. The cleanup utility processes data in batches. By adjusting batch size and pause intervals, you can tailor execution to match your workload, database size, and operational constraints.

The following parameters control cleanup behavior:

• ROWS_PER_BATCH: Number of rows processed in a single transaction.
• BATCH_SLEEP: Time (in seconds) the utility waits before processing the next batch.

The following table provides guidance for adjusting batch size and sleep intervals based on your environment:

Cron scheduling: Example (Linux)

Use cron to run database cleanup scripts automatically at scheduled times. The following examples show how to configure daily and weekly cleanup jobs and capture output in a log file.

# Edit crontab
crontab -e
# Run daily at 2 AM
0 2 * * * /opt/scripts/cleanup_mysql.sh >> /var/log/cleanup_cron.log 2>&1
# Run weekly (Sundays at 3 AM)
0 3 * * 0 /opt/scripts/cleanup_oracle.sh >> /var/log/cleanup_cron.log 2>&1

Windows task scheduler

Use Windows Task Scheduler to run the database cleanup script automatically at a defined time. Follow these steps:

1. Open Task Scheduler.
2. Select Create Basic Task.
3. Specify a name for the task. For example, database cleanup.
4. Set the trigger to Daily at 2:00 AM.
5. Set the action to Start a program.
6. Configure:
   • Program: C:\scripts\cleanup_mssql.bat
   • Arguments (optional): --loops 3 --sleep 10