Monitoring Database Housekeeping: Purging, Compacting, and Not Filling the Disk
Of all the disks you look after, the one under the monitoring database is the one you must never let fill. Every other full disk is an outage you will find out about. This one is the outage that stops you finding out about anything. When the monitoring database cannot write, the collectors stop recording. The graphs freeze at their last value. And the alert that should have told you is one of the things that stopped. The record of everything else ends at the moment you needed it most.
Keeping that from happening is housekeeping, and housekeeping is dull in exactly the way that matters. It means the same few checks, on a schedule, with the results written down. The retention tiers you designed are a promise the database makes about its own size. This guide is about making sure it keeps the promise, and about noticing early when it does not.
By the end you will be able to say what your purge job deletes and when. You will be able to prove that it ran and get the space back afterwards. You will be able to recognise index bloat before the graphs crawl and set free-space alarms that fire in time. And you will be able to run a weekly, monthly and quarterly checklist that takes less time than reading this sentence twice. Where the details depend on the database engine, I say so, and the answer is in the documentation for yours.
In this guide
- Why this disk is different
- The purge job: what it deletes and how to prove it ran
- Deleted rows do not shrink files
- Index bloat and slow graphs
- Free-space alarms on the monitoring server itself
- Backing up the database and testing the restore
- The housekeeping checklist template
- A daily check you can schedule
- War story, checklist, where to go next
Why this disk is different
A monitoring system watches everything except, by default, itself, and the disk under its database is where that blind spot bites. When a file server's disk fills, users complain, the monitoring alerts, and you fix it. When the monitoring database's disk fills, the sequence breaks at the second step. The collectors try to write and fail. Depending on the product, they may retry, drop samples, or stop. The web console keeps serving the last data it has, which looks like a network that has stopped changing. Nobody complains, because nothing the users touch has broken.
The failure is silent because the alarm and the fault share a disk. That is the whole problem, and it is why this guide spends a section on alarms that live somewhere else. The monitoring system failure modes guide catalogues the other ways a monitoring system dies quietly. A full database volume is the most common one I have seen, and the most preventable. The growth that fills it is arithmetic you can do in advance.
The database also fills fastest at the worst time. Incidents make devices flap, and every flap writes status rows, alert rows, and notification rows. A volume that has been ninety-five percent full for a month can go to one hundred percent in the first hour of a bad night. A quiet database is not evidence that the margin is enough. It is evidence that nothing has happened yet.
The purge job: what it deletes and how to prove it ran
The purge job is the scheduled task inside the monitoring product that enforces the retention tiers. Every night, typically during the quietest hour, it finds rows older than each tier's boundary. Where a rollup is configured, it writes rollup rows to replace those rows. Then it deletes the originals. The tiers guide covers what the boundaries should be; here the questions are what the job actually touched and whether it ran at all.
What it deletes depends on the product, and this is worth reading up on rather than assuming. Most purge raw samples past the raw boundary. Many also purge rolled-up rows past their boundaries, old alert history, old status changes, and old notification logs, each on its own timer. Some purge nothing you did not explicitly enable. Open the product documentation, list every table the purge touches, and write the retention for each next to it. If a table is not on the list, it grows forever.
Proving the purge ran is two queries. The first is the oldest row in the raw table. It should be about as old as the raw tier, give or take a day. If the raw tier is four weeks and the oldest row is from eight months ago, the purge has not run in seven of them. The second is rows per day, which shows both the write rate and, at the far end, a clean edge where the purge has been. The names below are illustrative; your product's tables and columns will differ.
-- Illustrative: table and column names differ by product. -- 1. Oldest raw sample: should be about one raw-tier ago. SELECT MIN(sample_time) AS oldest_raw FROM metric_samples; -- 2. Rows per day for the last week: write rate, and whether today is being written. SELECT DATE(sample_time) AS day, COUNT(*) AS rows_written FROM metric_samples WHERE sample_time >= CURRENT_DATE - 7 GROUP BY DATE(sample_time) ORDER BY day;
Look for two things in the second result. Today's row should be roughly the same size as yesterday's, scaled for the hour of the day. If it is zero, the collectors stopped. The disk is only one of the reasons that happens. And the oldest days should end sharply. A purge that runs is a cliff edge in the data. A purge that does not run is a long, gentle slope back to the day of the install.
Deleted rows do not shrink files
This is the part that surprises everyone once. A purge deletes rows, but the database file on disk does not get smaller. The engine marks the space inside the file as free and keeps it for its own reuse. From the operating system's point of view the file is exactly as large as it was. The free-space alarm you set on the volume does not move. The space is real, and it will be reused by tomorrow's rows, but it is inside the file, not on the disk.
For a database at steady state this is fine, and you should leave it alone. Each night's purge frees about as much space as the day's collection needs, and the file stops growing. Compacting is for the one-off case. You shortened a tier, or the purge had been broken for months and just deleted half the database. Now there are gigabytes of free space inside a file on a volume that needs them back. How you compact depends on the engine, and the exact commands are in the database documentation, but the shape is this.
- SQLite, the usual choice for small installs: the database is one file, and compacting rebuilds that file from scratch. It needs free space on the volume equal to the size of the database while it runs. It locks the database for the duration, so stop the monitoring service first and do it in a maintenance window.
- MySQL or MariaDB: tables can be rebuilt one at a time. When each table has its own file, rebuilding returns its free space to the operating system. It also needs working space and takes a lock, so schedule it and do the biggest table first.
- SQL Server: the data file can be shrunk, but shrinking scatters the indexes. So it is usually followed by an index rebuild. The usual advice is not to shrink at all unless a one-off purge has left a large gap. That gap must be one the database will not grow back into. The log file is a separate question with its own rules.
Whichever engine, treat compacting as a maintenance-window job with a backup before it. Check the product documentation as well as the database documentation. Some monitoring products expect to do this themselves. (Do not compact on a Friday. Nothing says "weekend" like a rebuild that stalls at ninety percent.)
Index bloat and slow graphs
An index is the sorted lookup structure that lets the database find one series' rows between two timestamps without reading the whole table. Indexes are built for tables that grow at the end and are read at random. A monitoring table grows at the end and is deleted from the beginning, every night, forever. Over months this leaves the index full of holes and out of order, a state called fragmentation. A fragmented index makes the engine read many more pages than it should to answer the same question.
The symptom is graphs that get slower for no visible reason. A week graph that took a second in spring takes eight seconds in autumn, and the console feels tired. People stop opening it, and a monitoring system nobody opens is a monitoring system with no users, which is a different kind of outage. The fix is index maintenance: reorganising or rebuilding the big indexes on a schedule, in a quiet hour. Every engine has a way to do this and a way to measure how fragmented an index is. The command differs, the idea does not.
Measure the symptom rather than guessing. Pick one graph, the same one every month, and time how long it takes to draw. Write the number in the housekeeping log. When it doubles, rebuild the indexes. If that does not fix it, the monitoring performance tuning guide has the wider list of suspects. The database backend choices guide is where to look if the honest answer is that the install has outgrown its engine.
Free-space alarms on the monitoring server itself
Monitor the monitoring server like any other server, and then do one thing more. Make sure at least one alarm about it can fire when the monitoring server itself cannot. The heartbeats and dead-man switches guide covers the mechanism, a second system that expects a regular signal and shouts when it stops. For the database volume, the checks that matter are these.
- Free space on the database volume, in days rather than percent. Take the daily growth from the storage math guide. Divide the free space by it. Alarm when the answer drops below the time it would take you to act. Ten percent of a large volume may be months; ten percent of a small one may be a weekend.
- Free space on the transaction log volume, where the engine keeps one. A purge that deletes months of rows in one night writes a large log, and the log volume can fill before the data volume does.
- Rows written today. Zero by mid-morning means the collectors have stopped, whatever the reason.
- Age of the oldest raw row. More than the raw tier plus two days means the purge has stopped.
- The purge job's own log or status, if the product exposes one. A job that reports "failed" every night for a month is not a subtle signal, but only if something reads it.
Set the thresholds so you have time, not so they are technically correct. An alarm at five percent free on a volume that grows by a percent a day gives you five days. Four of those will be a bank holiday. An alarm at fifteen percent on the same volume gives you two weeks. That is enough to order a disk, which is the only real fix.
Backing up the database and testing the restore
The monitoring database needs a backup like any other. And it needs one especially before every purge change, tier change, and compaction, because those are the operations that delete data on purpose. The monitoring backups guide covers the how: what to include, how to quiesce the database, where to keep the copy. This guide adds only the housekeeping angle.
Test the restore on a schedule, not on the day you need it. Once a quarter, restore the most recent backup to a spare machine, open the console against it, and draw one graph for last week. If the graph draws, write down the date and the time it took. If it does not, you have found out on a Tuesday afternoon instead of during an incident, which is the whole point of the exercise. And notice the backup's size while you are there. A backup that grows every quarter is a database whose purge is not keeping up, seen from a different angle.
The housekeeping checklist template
The checklist is the guide compressed to what you actually do. Print it, or put it in the ticket system as a recurring task, and record each run with a date and the numbers you saw. The numbers are the point. A housekeeping log with six months of database sizes and graph timings in it is a capacity plan for the monitoring server, written by accident.
| When | Check | Record | Act if |
|---|---|---|---|
| Weekly | Free space on the database volume and the log volume | Free space in GB and in days of growth | Days of headroom below your response time |
| Weekly | Oldest raw row | Its date | Older than the raw tier plus two days |
| Weekly | Rows written yesterday | The count | Zero, or far from last week's count |
| Weekly | Purge job status in the product | Last run time and result | Any failure or a missing run |
| Monthly | Database file size | Size in GB, compared with last month | Still growing after the tiers should have filled |
| Monthly | Time to draw one fixed reference graph | Seconds | Double the first recorded value |
| Monthly | Backup completed and its size | Date and size | Missing, failed, or growing every month |
| Monthly | Monthly summary exported to the archive | File name and checksum | Not done before the hourly tier ages out |
| Quarterly | Restore the latest backup to a spare machine and draw a graph | Date, duration, result | Restore fails or the graph does not draw |
| Quarterly | Index maintenance on the large tables | Date and fragmentation before and after | Fragmentation high or graphs slowing |
| Quarterly | Retention tier settings match the tier table | Any differences | They differ and nobody knows why |
| Quarterly | Free-space alarm thresholds still give enough days | Threshold and days it represents | Growth has risen and the threshold no longer buys time |
A daily check you can schedule
The weekly checks are easier when a scheduled task records the raw numbers every day and you only read them once a week. Two small pieces do the job. The first records the database file size to a text file; on Windows, run it from the task scheduler. The second is the row-count query from earlier, run against the database and appended to the same file. Both are illustrative: the path, the table name and the command-line tool are whatever your product and engine use.
# Illustrative: record the database file size once a day (Windows, scheduled task). # Adjust the path to your database file; for a client-server engine, query the # engine for its data and log file sizes instead. $db = "D:\Monitoring\history.db" $size = (Get-Item $db).Length "$(Get-Date -Format yyyy-MM-dd),$size" | Add-Content "D:\Monitoring\dbsize.csv"
-- Illustrative: append yesterday's row count to the same log. -- Run through your engine's command-line client from the same scheduled task. SELECT CURRENT_DATE AS logged, COUNT(*) AS rows_yesterday FROM metric_samples WHERE sample_time >= CURRENT_DATE - 1 AND sample_time < CURRENT_DATE;
After a week, the size file tells you the real growth per day, which is the number the free-space alarm needs. After a month it tells you whether the purge is holding the line. After a year it is the graph you show whoever signs off the next disk. Small, dull, and daily beats clever and occasional every time.
Tip: put the daily check's output somewhere other than the database volume. A log that cannot be written because the disk it is warning about is full is a joke the disk has made before.
A war story from Bellweather Logistics
Bellweather Logistics kept its monitoring database on the same volume as the operating system, with about four percent free, which had been "fine for ages." One night a storage array developed a fault that made every host attached to it flap between up and down. Every flap wrote status rows, alert rows and notification rows to the database. The volume filled at 03:40, forty minutes into a six-hour incident, and the collectors stopped writing. The console kept showing the last state it had recorded. And the on-call engineer, seeing the dashboard, concluded things had calmed down and went back to sleep. The post-incident review reconstructed the timeline from switch logs and noted that the monitoring system had "captured the onset in excellent detail." The database volume now has its own disk and a free-space alarm that reports in days.
Checklist
- The monitoring database sits on its own volume, not the operating system's.
- You have a list of every table the purge touches, with the retention for each.
- The oldest raw row is about one raw tier old, and you checked this week.
- Rows written yesterday matches the storage math, and today is being written.
- You know that deleted rows do not shrink the file, and you compact only after a one-off purge, in a window, with a backup.
- One reference graph is timed every month and the number is written down.
- Free-space alarms are set in days of headroom, and at least one can fire when the monitoring server cannot.
- The database backup runs, its size is recorded, and the restore is tested quarterly.
- The housekeeping checklist exists as a recurring task with a log.
- The daily size and row-count check writes to a different volume.
Where to go next
Housekeeping keeps the database the size the tiers promised. The last piece is what happens to data that leaves the database on purpose. That includes the monthly summaries and incident snapshots that go to the archive, and the proof that you can get them back. This is covered in archiving old monitoring data. The whole pillar, from the stakeholders to the archive, is at Retention and Granularity: How Long to Keep What. Run the checks, write the numbers down, and the disk stops being the one who decides when the record ends.
Put this into practice with SysPeep Monitor
Housekeeping is simpler when the database is one you can open, query and back up with ordinary tools. SysPeep Monitor stores its history in SQLite for small installs, or MySQL/MariaDB or SQL Server for larger ones. It serves history, graphs and reports from one web console. For its purge schedule and retention settings, check the product documentation. The free trial installs on any Windows machine.
Download Free TrialLearn About SysPeep