Stop your Traccar database growing forever — automated cleanup with bash and cron

Traccar saves every position every device sends, and by default it never deletes any of them. A small fleet reporting every 10 seconds can add millions of rows a month. After a year or two the tc_positions table is tens of gigabytes. Reports get slow, backups take hours and the disk fills up.

Most fleets don't need raw GPS points from three years ago. This post shows a small bash script that deletes old positions and events in batches, and a cron job that runs it every night.

Get the script on GitHub: gennissi/traccar-cleanup has the script, a cron example, the MySQL user setup and a README.

Check how big the problem is

Start by checking which tables are using the space. In the MySQL client:

SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb,
       table_rows
FROM information_schema.tables
WHERE table_schema = 'traccar'
ORDER BY (data_length + index_length) DESC
LIMIT 5;

Almost always it's tc_positions, followed by tc_events. Every other table is tiny next to those two.

Decide how long to keep data

Pick a retention period that fits your customers before you delete anything. Some common choices:

Use case	Typical retention
Personal or family tracking	30–60 days
Commercial fleet	90–180 days
Contracts that need audit trails	1 year or more

The script below defaults to 90 days. You can change it with one variable.

Back up first. Take a dump before the first run: mysqldump --single-transaction traccar | gzip > traccar-$(date +%F).sql.gz

Why not one big DELETE?

DELETE FROM tc_positions WHERE servertime < ... on a table with 100 million rows is a bad idea:

• It runs as one huge transaction that can take hours and bloat the undo log.
• It holds locks while Traccar keeps trying to insert new positions.
• If it fails halfway, MySQL has to roll the whole thing back, which takes about as long again.

Deleting 10,000 rows at a time with a short pause between batches keeps every transaction small, and Traccar keeps running normally while it works.

There is one more catch. Each device's latest position is stored in tc_devices.positionid. If you delete that row, a device that has gone quiet will lose its last known location on the map. The script never deletes those rows.

The cleanup script

Save this as /usr/local/bin/traccar-cleanup.sh, or download it from GitHub:

#!/usr/bin/env bash
# Delete Traccar positions and events older than $DAYS, in small batches.
set -euo pipefail

DAYS="${DAYS:-90}"          # how many days of history to keep
BATCH="${BATCH:-10000}"     # rows deleted per transaction
DB="${DB:-traccar}"
DRY_RUN="${DRY_RUN:-0}"     # set to 1 to only count rows

MYSQL=(mysql --defaults-extra-file=/etc/traccar-cleanup.cnf -N -B "$DB")

log() { echo "$(date '+%F %T') $*"; }
q()   { "${MYSQL[@]}" -e "$1"; }

# servertime is when the row was inserted, so it grows with the id.
# Find the first id we keep; everything below it is old.
keep_from=$(q "SELECT id FROM tc_positions
               WHERE servertime >= NOW() - INTERVAL $DAYS DAY
               ORDER BY id LIMIT 1")
if [[ -z "$keep_from" ]]; then
  keep_from=$(( $(q "SELECT COALESCE(MAX(id), 0) FROM tc_positions") + 1 ))
fi

old_positions=$(q "SELECT COUNT(*) FROM tc_positions WHERE id < $keep_from")
old_events=$(q "SELECT COUNT(*) FROM tc_events
                WHERE eventtime < NOW() - INTERVAL $DAYS DAY")
log "Keeping $DAYS days. Old positions: $old_positions, old events: $old_events"

if [[ "$DRY_RUN" == "1" ]]; then
  log "Dry run, nothing deleted."
  exit 0
fi

delete_in_batches() {
  local label=$1 sql=$2 total=0 n
  while :; do
    n=$(q "$sql LIMIT $BATCH; SELECT ROW_COUNT();")
    total=$(( total + n ))
    (( n < BATCH )) && break
    sleep 0.5   # give Traccar's inserts room to breathe
  done
  log "Deleted $total $label"
}

delete_in_batches "events" \
  "DELETE FROM tc_events WHERE eventtime < NOW() - INTERVAL $DAYS DAY"

# Never delete a device's latest position (tc_devices.positionid).
delete_in_batches "positions" \
  "DELETE FROM tc_positions
   WHERE id < $keep_from
     AND id NOT IN (SELECT positionid FROM tc_devices WHERE positionid IS NOT NULL)"

log "Done."

Make it executable:

sudo chmod 750 /usr/local/bin/traccar-cleanup.sh

Keep the password out of the script

The script reads its login from a MySQL options file, so the password never appears in the script, in ps output or in your shell history. Create /etc/traccar-cleanup.cnf:

[client]
user=traccar_cleanup
password=CHANGE_ME
host=127.0.0.1

Lock it down with sudo chmod 600 /etc/traccar-cleanup.cnf. Ideally, give the script its own MySQL user that can only SELECT and DELETE on the Traccar tables:

CREATE USER 'traccar_cleanup'@'127.0.0.1' IDENTIFIED BY 'CHANGE_ME';
GRANT SELECT, DELETE ON traccar.tc_positions TO 'traccar_cleanup'@'127.0.0.1';
GRANT SELECT, DELETE ON traccar.tc_events    TO 'traccar_cleanup'@'127.0.0.1';
GRANT SELECT         ON traccar.tc_devices   TO 'traccar_cleanup'@'127.0.0.1';

Test with a dry run

Always run it in count-only mode first:

sudo DRY_RUN=1 /usr/local/bin/traccar-cleanup.sh

You should see one line with the number of rows it would delete. If the numbers look right, run it for real. The first run on a big database can take a while. Later nightly runs only remove one day's worth of rows and finish in seconds.

Run it every night with cron

Add this with sudo crontab -e:

# Traccar cleanup at 03:15 every night
15 3 * * * flock -n /run/traccar-cleanup.lock /usr/local/bin/traccar-cleanup.sh >> /var/log/traccar-cleanup.log 2>&1

flock -n stops a second copy from starting if one run is still going, which can happen on the first night. Check /var/log/traccar-cleanup.log the next morning to confirm it ran.

Getting the disk space back

InnoDB doesn't give freed space back to the operating system by itself. Deleted rows become free space inside the table file, and new positions reuse it, so the table stops growing. That alone is usually enough.

If you need the disk space back now, rebuild the table once after the first big cleanup:

OPTIMIZE TABLE tc_positions;

This rewrites the whole table, so it needs free disk space about the size of the table's remaining data and can take a long time on large tables. Run it in a quiet period, after a backup.

What about Traccar's built-in option?

Recent Traccar 5.x releases include a database.historyDays setting that deletes old history on a schedule. If your version supports it, check the configuration docs for your exact release and try it. A separate script is still useful when you:

• run an older Traccar version,
• want to control batch size and timing yourself,
• need to keep each device's last position, or
• want a dry run and a log to show a client.

Summary

1. Check which tables are big.
2. Agree a retention period with whoever owns the data.
3. Back up.
4. Do a dry run, then run the batch delete.
5. Schedule it nightly with cron and flock.
6. Optionally run OPTIMIZE TABLE once to get disk space back.

After that the database stays roughly the same size, reports stay fast and you don't get a full disk at 3 a.m.