Table cleaner

  • Release version: Washingtondc
  • Updated February 1, 2024
  • 2 minutes to read
    • Summarize
    Summarized using AI
    This content was generated using new OpenAI-powered functionality. Results are provided on an as is basis and are not guaranteed to be accurate or complete.

    Summary of Table Cleaner

    The Table Cleaner is a scheduled job within the ServiceNow AI Platform that automatically deletes older, expired, or unwanted records from tables to prevent excessive data growth. By default, it runs once per hour, enhancing query performance and maintaining manageable table sizes.

    Show full answer Show less

    Key Features

    • Automatic Deletion: Deletes records based on specified rules, targeting specific tables, conditions, and record ages.
    • Auto Flush Rules: View rules in the Auto Flush table (sysautoflush) for base system tables and their record ages.
    • Matchfield Configuration: Each rule must specify a Matchfield (typically a date field) and an Age in seconds to determine when records are deleted.
    • Performance Considerations: Slow rule handling can occur if queries lack indexing, potentially stopping the job if a query exceeds 30 seconds.
    • Disable Option: Administrators can restrict table cleaner functionality on specific tables by adding the Disable Table Cleaner attribute.
    • Limitations: Not supported on tables with rotation or extension, and legacy rules from these configurations can be ignored.

    Key Outcomes

    By activating Table Cleaner, ServiceNow customers can efficiently manage their data, ensuring that tables do not become unmanageable and performance remains optimal. However, customers should be mindful of the configuration of rules and the limitations in place to maximize the effectiveness of the Table Cleaner.

    Table cleaner deletes older records automatically and prevents data from growing exponentially.

    Table cleaner is a scheduled job that runs once per hour (by default) to delete older, expired, or unwanted records from tables. Table cleaner prevents tables from growing to an unmanageable size and improves query performance.

    You can view a list of table cleaner rules in the Auto Flush [sys_auto_flush] table by typing sys_auto_flush.list in the filter navigator. The Auto Flush table displays rules for base system tables and their corresponding record ages. Multiple rules can appear for a single table if each rule has a unique condition defined.

    Which records are deleted

    Each table cleaner rule specifies the following:

    • The target table (to delete records from).
    • A Matchfield value that corresponds to a Date/Time column in the target table. Ideally, the Matchfield is a date field that represents how long the record has been active (for example, sys_created_on) or how long since the last update (sys_updated_on).
    • An Age in seconds value that determines when the delete is triggered.
    • Options for cleaning related records in peripheral or audit tables.
    • One or more optional conditions to filter which records are deleted. For example, you might specify that only records where 'active = false AND state =closed' are deleted.

    When the Matchfield date is farther in the past than the Age in seconds value, the table cleaner job deletes the record.

    Slow rule handling

    When the table cleaner job runs, each table cleaner rule runs several queries as part of the process. If there's no index on a rule's match field or on significant portions of its condition, rule processing can be slow because its queries are running inefficiently on large amounts of data.

    If a table cleaner rule has a query that takes longer than 30 seconds to complete, the entire table cleaner job is stopped. By default, table cleaner waits two days before including that rule in the table cleaner job again, which enables the table cleaner job to run without disruption in the meantime. You can configure the duration of the waiting period by adding a system property. See Table cleaner properties.

    Disabling table cleaner

    You can prevent an administrator from creating a table cleaner rule or running the table cleaner on a specific table by adding the Disable Table Cleaner attribute to the table's dictionary record. Some internal system tables have the Disable Table Cleaner attribute added by default.

    Table cleaner limitations

    Note the following limitations.

    • Table cleaner rules aren't supported for tables configured with table rotation or table extension. Some tables in your instance might have legacy table cleaner rules that were established before table rotation or table extension were enabled. These legacy rules can be safely ignored.
    • Performance depends on the size of the table and the conditions you specify. For example, if you use a custom column without an index in a large table, performance is severely degraded. Performance also depends on the number of rows to be deleted.
    • Table cleaner spends a maximum of 20 minutes to delete records from a single table. If queries are slow, the volume of records deleted in the 20-minute period may be small.
    • Table cleaner does not call DBDelete.setWorkflow(). This means DBDelete objects run with workflow=false (false is the default value for a Java Boolean), hence Delete business rules and workflows are not triggered.