All jobs / Database administrator
Maintenance interview questions
Maintenance questions a Database administrator can be asked, from first job to senior. Open one to see the strongest answer and why it works.
What goes into a database’s routine maintenance schedule, and how often?
The strongest answer: Backups with a restore test, integrity checks, statistics kept current, index maintenance where justified, cleanup of old data and log files, and a space check — backups daily, the rest weekly
Why it works: Right — and the one people leave out is the restore test, which is the only proof the backups are real.
- Back up, then test the restore.
- Check integrity regularly.
- Keep statistics current.
- Maintain indexes only where justified.
An events table holds six years of data and only the last three months are queried. Options?
The strongest answer: Agree a retention rule with the owners, move older rows to an archive table or cheaper storage in batches, and partition by time so future archiving is a partition drop
Why it works: Exactly — retention is a business decision; batches and partitions make it routine.
- Agree retention with owners.
- Archive in batches.
- Partition by time for the future.
- One giant delete is an outage.
The `sessions` table holds a few hundred thousand live rows but occupies tens of gigabytes.
A table keeps growing on disk even though rows are deleted daily. What is happening?
The strongest answer: Deleted and updated rows leave dead versions behind under MVCC and clean-up is not keeping up — often a long-running transaction holds it back — so I check the clean-up activity and remove the blocker
Why it works: Exactly — MVCC leaves the old versions; something must clear them and something may be preventing it.
- MVCC leaves dead versions.
- Clean-up must keep pace.
- Long transactions block it.
- Rebuild is the exception, not the routine.
When do you rebuild an index versus leaving it alone?
The strongest answer: When measured bloat or fragmentation is hurting reads or wasting space, and then online if the engine supports it; a fixed nightly rebuild of everything is mostly wasted I/O
Why it works: Exactly — measure, then act on the indexes that need it, with minimal blocking.
- Measure bloat first.
- Rebuild what needs it, online.
- Nightly everything is waste.
- Slow query needs diagnosis.
How would you know if silent data corruption crept into the database?
The strongest answer: Enable page checksums or the engine’s page verification, run periodic integrity checks, keep backups long enough to reach back before the corruption, and alert on the first checksum error
Why it works: Exactly — detection has to be switched on, and retention must outlast the discovery delay.
- Turn on checksums.
- Schedule integrity checks.
- Retention outlasts discovery.
- Alert on the first error.
A table of hundreds of millions of rows takes new data all day, and its plans go bad by the afternoon.
How do you keep its statistics good enough?
The strongest answer: Automatic updates fire only after a share of the rows change, which this table reaches late — so I schedule updates on the columns plans depend on, usually the ascending date key
Why it works: Exactly — the threshold is proportional, so scale defeats it, and the ascending key is where the plan goes wrong first.
- Auto thresholds are proportional.
- Big tables reach them late.
- Schedule the columns that matter.
- Sample enough, then verify plans.
Automatic clean-up of dead row versions cannot keep up on the busiest table. What do you tune?
The strongest answer: Per-table clean-up thresholds and cost limits so it runs more often and harder on that table, remove long-running transactions that hold back the horizon, and consider partitioning so each piece is small
Why it works: Exactly — tune per table, remove blockers, shrink the unit of work.
- Tune per table, not globally.
- Kill the long transactions.
- Partition the busiest table.
- Disk is not a fix for bloat.
The business expects growth next year. How do you plan database capacity?
The strongest answer: Trend storage, IOPS, connections and the largest tables from monitoring, tie them to the business drivers, set headroom and alert thresholds, and revisit quarterly — rather than one big guess
Why it works: Exactly — measured trends tied to drivers, with headroom and a review cadence.
- Trend from real metrics.
- Tie growth to business drivers.
- Headroom plus early alerts.
- Review regularly.
The full integrity check on a multi-terabyte database no longer finishes inside the maintenance window.
How do you keep checking for corruption?
The strongest answer: Restore the latest backup elsewhere and run the full check there, which also proves the backup; keep page checksums and alerting on the primary, and stagger its own check across nights
Why it works: Exactly — the offloaded check proves backup and data together, but it cannot see the primary’s own storage, so checksums and alerting stay on.
- Check a restored copy elsewhere.
- That proves the backup too.
- Keep checksums and alerts on the primary.
- Stagger the primary’s own check.
You own several hundred databases and are asked to cut the total cost meaningfully. Approach?
The strongest answer: Inventory and tag them, find the idle and oversized ones from utilisation data, consolidate small ones, right-size the big ones with measurements, review retention and replica counts, and report savings against a baseline
Why it works: Exactly — a data-driven programme with a baseline, not a slogan.
- Inventory and tag.
- Idle and oversized first.
- Consolidate the small ones.
- Report against a baseline.
How do you forecast capacity for a fleet rather than one database?
The strongest answer: Classify databases by workload profile, model each class’s growth from telemetry and the product roadmap, keep headroom rules per class, and turn the model into a quarterly purchase or reservation plan with a review
Why it works: Exactly — classes, telemetry, roadmap, and a decision cadence.
- Classify by workload.
- Model growth per class.
- Include the roadmap.
- Review and adjust quarterly.
Legal wants some data deleted after a fixed period and other data kept for years. How do you build that into the platform?
The strongest answer: A retention schedule per data class agreed with legal, enforced by time-partitioned tables and automated jobs, with backups aligned so deleted data does not linger, and evidence that the deletions ran
Why it works: Exactly — classes, enforcement, backup alignment and proof.
- Retention per data class.
- Enforce with partitions and jobs.
- Align backup retention.
- Keep evidence of deletion.