Background scripts without regret

You hit this the day someone notices clones are taking twice as long, or a list that used to load instantly now takes eight seconds, and you go looking and find em_event holding two million processed events from a monitoring integration nobody ever tuned. Or sys_email bloated with five years of sent mail. The fix is a background script, and a background script is the sharpest tool in the platform: no update set, no review gate, no undo button. I once watched a colleague point a delete loop at task when he meant a child table. The query was fine. The table was not. This recipe is the discipline that keeps that from being you.

The quick version

Here is the whole thing, ready to paste. It is a dry run by default and it will not touch anything until you deliberately change that.

// Stale event cleanup. DRY RUN by default: read the output before you flip the flag.
var actuallyDelete = false; // flip to true by hand, per run. Never store it flipped.

var TABLE = 'em_event';
// Build this in a filtered list first, then right-click the breadcrumb and copy the query.
var QUERY = 'state=Processed^sys_created_on<javascript:gs.daysAgoStart(90)';
var CHUNK = 1000;
var LOG = '[EVT_CLEANUP] ';

// Count first. Always.
var agg = new GlideAggregate(TABLE);
agg.addEncodedQuery(QUERY);
agg.addAggregate('COUNT');
agg.query();
var total = 0;
if (agg.next()) {
    total = parseInt(agg.getAggregate('COUNT'), 10);
}
gs.info(LOG + total + ' records match on ' + TABLE + ': ' + QUERY);

if (!actuallyDelete) {
    var sample = new GlideRecord(TABLE);
    sample.addEncodedQuery(QUERY);
    sample.setLimit(5);
    sample.query();
    while (sample.next()) {
        gs.info(LOG + 'WOULD delete ' + sample.getUniqueValue() +
            ' (created ' + sample.getValue('sys_created_on') + ')');
    }
    gs.info(LOG + 'Dry run complete. Nothing was touched.');
} else {
    var deletedTotal = 0;
    var batch = 0;
    var keepGoing = true;
    while (keepGoing) {
        var gr = new GlideRecord(TABLE);
        gr.addEncodedQuery(QUERY);
        gr.setLimit(CHUNK);
        gr.setWorkflow(false); // deliberate: no business rules, no notifications
        gr.query();
        var deletedThisBatch = 0;
        while (gr.next()) {
            gr.deleteRecord();
            deletedThisBatch++;
        }
        deletedTotal += deletedThisBatch;
        batch++;
        gs.info(LOG + 'batch ' + batch + ': ' + deletedThisBatch +
            ' deleted, ' + deletedTotal + ' of ' + total + ' total');
        if (deletedThisBatch === 0) {
            keepGoing = false; // nothing left, or nothing deletable. Either way, stop.
        }
    }
    gs.info(LOG + 'Done. ' + deletedTotal + ' records deleted.');
}

Notice what is not in there: no hardcoded sys_ids. The script targets records by state and age, conditions a human can read and argue about, not opaque identifiers that mean something different on every instance.

Building it step by step

Step one: build the query where you can see it. Never compose an encoded query from memory inside a script. Filter a list in the UI, eyeball the results, page through a few screens, then right-click the breadcrumb and copy the query. If the list shows you records you did not expect, you just saved yourself an incident.

Step two: count before you touch. The GlideAggregate count is non-negotiable. If you believe you are about to delete 300,000 records and the count says 4.7 million, stop. Your query is wrong, or your understanding of the data is, and either one is disqualifying.

Step three: log what would change. The dry-run branch prints a handful of sample records with their sys_ids and creation dates. Read them. Open one or two in the UI. Confirm they are what you think they are. This step takes ninety seconds and it has caught a wrong query for me more times than I want to admit.

Step four: the flag you flip by hand. actuallyDelete starts false and only a human edit changes that, immediately before a run, for that run only. Do not keep a copy of the script saved with the flag set to true. The whole point is that the destructive version never exists at rest.

Step five: chunk the destructive pass. The loop re-queries with setLimit(CHUNK) until a batch comes back empty. Chunks of 1,000 keep each unit of work short, give you a progress line per batch, and mean that if you cancel the transaction halfway you have a clean, known state instead of a half-finished mystery. The empty-batch guard also protects you from an infinite loop if some records refuse to delete.

Step six: decide what to suppress, out loud. The example calls setWorkflow(false) and says why in a comment. That is a real decision, not boilerplate, and the next section explains what you just decided.

What is actually happening under the hood

deleteRecord() is not a database delete. It walks the record through the platform engines: delete-time business rules fire, cascade rules clean up or orphan references depending on the dictionary settings, auditing writes its trail, and notifications can trigger. On a big table that overhead is most of your runtime, and sometimes most of your risk.

setWorkflow(false) switches those engines off for the operations this GlideRecord performs: business rules, workflows and flows, notifications, SLA calculation, and, the one people forget, field auditing. For stale event logs that is exactly right, because nothing downstream should care. It is exactly wrong when you are touching task records where SLAs need to cancel cleanly, where compliance expects an audit trail, or where a delete business rule does cleanup you actually want. Suppression is a scalpel, not a default.

autoSysFields(false) is the update-side cousin. It stops the platform from stamping sys_updated_on, sys_updated_by, and sys_mod_count. For deletes it does nothing useful, so I left it out of the centerpiece, but on a mass update it is the difference between a quiet backfill and every integration that watches sys_updated_on waking up at once to sync a million "changed" records. Skip it when you want downstream systems to notice; use it when the change is cosmetic and the stampede would hurt.

Then there is deleteMultiple(). One call, no per-record scripting, dramatically faster, and it skips the row-by-row overhead. The tradeoffs: you give up the progress log, you give up the ability to throttle, and there are sharp edges (see the warning below). My rule: deleteMultiple for genuinely inert log tables where the whole set can go in one statement, iterated deleteRecord in chunks anywhere I want observable, cancellable progress.

Gotchas

A few more that have bitten real people. Check the scope picker at the top of the background script page: run in the scope that owns the table, or cross-scope access rules will either block you or, worse, half-work. Watch transaction length: a single monster run holds a semaphore and can trip transaction quota rules, so for millions of rows move this same loop into a scheduled script execution and let it run off-hours in the background. Know your cascade rules before you delete parents, because deleting one record can quietly take its children with it. And rehearse on a clone first: same script, same query, real data volume, zero stakes. If your numbers on the clone do not match your expectations, production would not have either.

Variations worth knowing

Make retention permanent with a table cleanup policy. If you are deleting the same stale records every quarter, stop. Create a record in sys_auto_flush (Table Cleanup) with the table, an age in seconds, and conditions, and let the platform's hourly cleaner enforce retention forever. The one-off script is for the backlog; the policy is for the future.

The scheduled version. Wrap the delete loop in a scheduled script execution that runs nightly and caps itself at, say, 50 batches per run. It chews through the backlog over a week with no long transactions and no one babysitting it.

The mass-update variant. Same skeleton, but the destructive branch calls setValue and update() instead of deleteRecord(). This is where autoSysFields(false) earns its place, and where you should think hardest about whether setWorkflow(false) is right, because updates have far more downstream listeners than deletes of old logs do.

Archive before you delete. For anything with even faint audit value, export the candidate set to an attachment or push it through your archiving setup first. Storage is cheap. The conversation about the records you cannot get back is not.