DBAtlas · Database operations atlas

Disk / tablespace full

You got 'unable to extend', 'disk full', 'could not extend' or 'log full'. Don't panic: first find WHAT is full, reclaim space safely, then fix it permanently.

What is full?

Cleaning the wrong place does not help. Measure usage first: is it a datafile, the transaction log, archive/redo, temp, or the OS disk?

SELECT d.tablespace_name, ROUND((1-(d.alloc-NVL(f.free,0))/d.maxb)*100,1) AS pct_free
FROM (SELECT tablespace_name, SUM(bytes) alloc, SUM(GREATEST(bytes,maxbytes)) maxb FROM dba_data_files GROUP BY tablespace_name) d LEFT JOIN (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) f ON f.tablespace_name = d.tablespace_name ORDER BY pct_free;
  • Datafile / tablespace is full
  • Transaction log / archive-redo is full
  • Temp / undo is full
  • OS disk is full (not the DB)

What is the biggest consumer?

Most of the time a single runaway table/segment is the culprit. Find the largest ones.

SELECT segment_name, segment_type, ROUND(bytes/1048576,1) AS mb FROM dba_segments ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY;
  • There is an unexpectedly large/runaway table
  • Normal growth, space has genuinely run out
  • Too many dead rows / bloat

Why is the log growing?

The log/archive cannot be cleaned up until the oldest active transaction/backup completes. Why has it accumulated?

SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name=DB_NAME();  -- why can it not be truncated?
  • MSSQL: log backups are not being taken (LOG_BACKUP)
  • PG: a stalled replication slot is retaining WAL
  • Oracle: archived log area (FRA) is full
  • A long open transaction is holding the log

Root cause: Runaway table/segment

A single object has grown unexpectedly (uncontrolled inserts, a queue/log table that is never purged).

Root cause: Genuine capacity need

Normal growth; space must be added.

Root cause: Bloat / dead rows

The space of deleted/updated rows has not been reclaimed.

Root cause: Temp / undo full

A large sort/hash or a long open transaction filled the temporary space.

Root cause: OS disk full

The problem is on the operating system disk, not the DB. Logs, core dumps, old backups or trace files may be taking up space.

Root cause: Log backups not being taken (SQL Server)

In the FULL recovery model, the log grows indefinitely if no log backups are taken.

Root cause: Abandoned replication slot (PG)

A stalled (inactive) slot prevents WAL from being removed and bloats pg_wal.

Root cause: Archived log area (FRA) full (Oracle)

Archived logs have accumulated and the FRA is full; once the archiver stalls, the database can stop accepting writes.

Root cause: Long open transaction

The oldest active transaction is preventing log/undo from being reclaimed.

Open in DBAtlas →