Media Failure

The click. Every database administrator dreads it—that distinctive clicking sound a hard drive makes when its read/write heads fail, repeatedly seeking and failing to find the data tracks. In a fraction of a second, years of accumulated data becomes inaccessible. Unlike a power outage where memory is lost but disk survives, a media failure destroys the very persistent storage that was supposed to be our safety net.

Media failures represent the most severe category of database failures. They damage or destroy the persistent storage that normally survives system crashes. The log files that enable crash recovery might themselves be lost. The data files containing the actual database content might be corrupted or unreadable.

This page examines media failures in comprehensive detail—their causes, their devastating effects, and the specialized recovery strategies they require. Understanding media failures is essential because they demand a fundamentally different approach to recovery: backups, archives, and geographic redundancy.

A media failure (also called a hard failure or disk crash) occurs when persistent storage becomes unreadable, corrupted, or physically destroyed. The key characteristics are:

Formal Definition:

A media failure is an event where:

1. Some or all of the persistent storage (data files, log files, or both) becomes unavailable
2. The stored data cannot be read or is corrupted beyond use
3. Standard log-based crash recovery cannot restore the database
4. External backups or redundant copies are required for recovery

Media failures are 'hard' failures because they damage the durable storage that was supposed to persist data indefinitely. Unlike system failures where the disk survives and enables recovery, media failures strike at the foundation of durability itself.

Categories of Media Failure:

Media failures can be categorized by what is affected:

1. Data File Failure: The files containing database tables and indexes are damaged. The transaction log may be intact, allowing some recovery.

2. Log File Failure: The transaction log is damaged. This is particularly dangerous because the log is needed for recovery. Without the log, crash recovery is impossible.

3. Complete Media Failure: Both data files and log files are lost. This requires full restoration from backup plus replaying archived log files.

4. Partial Media Failure: Some storage is damaged but other storage is intact. Just a single disk fails in an array, or corruption affects only certain files.

5. Controller/Channel Failure: The storage interface fails, making otherwise intact disks inaccessible. This may be recoverable by replacing hardware.

Media failures arise from various sources spanning hardware wear, manufacturing defects, environmental factors, and human errors. Understanding these causes is essential for implementing appropriate preventive measures.

Categories of Media Failure Causes:

2.1 Hard Disk Drive (HDD) Failures

HDDs use spinning magnetic platters with read/write heads flying nanometers above the surface. This mechanical complexity creates multiple failure modes:

Head Crash: The read/write head contacts the spinning platter, damaging both the head and the magnetic surface. Causes include: vibration, shock, contamination, power interruption during operation.

Motor/Bearing Failure: The spindle motor or its bearings fail, preventing the platters from spinning. Common in older drives or drives operating in high-temperature environments.

PCB Failure: The controller board fails due to power surge, component aging, or manufacturing defect. Sometimes recoverable by swapping PCBs (but risky).

Firmware Corruption: The firmware stored on the drive's internal storage becomes corrupted, rendering the drive unusable even if the platters are fine.

2.2 Solid State Drive (SSD) Failures

SSDs have no moving parts but are subject to different failure modes:

Write Exhaustion: Flash cells can only be written a limited number of times (typically 1,000-100,000 cycles depending on technology). Enterprise SSDs track wear level and warn before failure.

Read Disturb: Repeated reads of certain cells can disturb adjacent cells, causing data corruption. Modern SSDs mitigate this with background refresh operations.

Sudden Power Loss: SSDs with inadequate power-loss protection can corrupt their mapping tables or lose unflushed data during unexpected power loss.

Controller/Firmware Bugs: SSDs have complex controllers running firmware. Bugs can cause data loss, performance degradation, or complete failure.

Retention Loss: Stored data in powered-off SSDs can degrade over time (months to years), especially at high temperatures. Enterprise SSDs are rated for longer retention.

2.3 Environmental and Catastrophic Causes

Some media failures affect entire facilities:

Fire: Direct fire damage destroys drives. Smoke and soot contaminate internals. Sprinkler systems cause water damage. Heat damages electronics.

Flood: Water damages electronics and can contaminate platter surfaces. Data centers in flood zones require special protection.

Earthquake: Shock and vibration cause head crashes. Building collapse destroys equipment. Power infrastructure may be damaged.

Temperature/Humidity: Extreme temperature variations stress components. High humidity causes condensation and corrosion. Low humidity increases static risk.

2.4 Human Error and Malicious Acts

Not all media failures are accidental:

Accidental Deletion:

-- The infamous command that should never be run without WHERE
DELETE FROM critical_table;
-- Or worse:
DROP DATABASE production;

Ransomware: Malware that encrypts files and demands payment. Database files and backups are prime targets. Offline backups are essential protection.

Intentional Destruction: Disgruntled employees or attackers deliberately destroying data. Requires access controls, audit logging, and off-site backups.

Standard crash recovery (Analysis → Redo → Undo) cannot handle media failures because it assumes the disk is intact. Let's understand precisely why:

The Crash Recovery Assumption:

Crash recovery works by:

1. Reading the log from disk
2. Identifying what needs to be redone/undone
3. Reading data pages from disk
4. Applying changes from the log to fix pages
5. Writing corrected pages back to disk

Every step assumes disk access works. Media failure breaks this assumption.

Scenario Analysis:

Case 1: Data Files Lost, Log Intact

This is actually the most recoverable media failure scenario:

• Restore data files from the most recent backup
• Apply archived log files to bring backup up to date
• Apply online (current) log to reach crash-consistent state
• Run normal crash recovery

Recovery is possible but may take hours depending on backup age and log volume.

Case 2: Log Files Lost, Data Intact

This is extremely problematic:

• Data files may contain uncommitted changes (need undo, but no undo info)
• Data files may be missing committed changes (need redo, but no redo info)
• Database is in unknown state
• Options: Accept potential inconsistency, restore from backup

This scenario is why log files should be on separate physical storage from data files.

Case 3: Both Data and Log Lost

Complete reliance on backups:

• Restore from last full backup
• Apply all archived logs since backup
• ALL work since last archived log is lost

The gap between last archived log and failure point is the data loss window.

Recovering from media failures requires preparation—specifically, backups and archived logs that were created before the failure occurred. Let's examine the recovery strategies:

The Recovery Building Blocks:

Strategy 1: Point-in-Time Recovery (PITR)

PITR restores the database to a specific moment in time:

1. Restore base backup — Write the backup files to the new storage
2. Apply archived logs — Replay logs from backup time forward
3. Apply up to target time — Stop applying logs at desired point
4. Open database — Allow new connections

PITR is used for:

• Recovering from media failure to the most recent possible point
• Recovering from logical errors (e.g., accidental DELETE) by restoring to before the error
• Creating database copies at specific historical points

Strategy 2: Restore with Forward Recovery

This applies all available logs to reach the most current state:

1. Restore base backup
2. Apply ALL archived logs — Every log from backup to failure
3. Apply surviving online logs — If any online log files survived
4. Run crash recovery — Handle transactions active at failure time
5. Open database

Strategy 3: Incremental Restore

For large databases with incremental backups:

1. Restore last full backup
2. Apply incremental backups — In chronological order
3. Apply archived logs — From last incremental forward
4. Apply surviving online logs
5. Run crash recovery

Incremental restore is faster than full restore when incrementals are available because less data needs to be copied.

While we cannot prevent individual disk failures, we can design systems where no single media failure causes data loss. This is achieved through redundancy at multiple levels:

Level 1: RAID (Redundant Array of Independent Disks)

RAID 10 is the gold standard for database storage:

• Combines mirroring (RAID 1) with striping (RAID 0)
• Survives multiple disk failures if they're in different mirror pairs
• Fast rebuild compared to parity-based RAID
• Excellent read and write performance
• Uses 50% of raw capacity for redundancy (acceptable trade-off)

Level 2: Separate Storage for Logs

As we've emphasized, log files should be on physically separate storage from data files:

• Different disk drives (at minimum)
• Different RAID arrays (better)
• Different storage controllers (even better)
• Different storage systems (ideal)

If data disks fail, the logs survive for recovery. If log disks fail, immediate action is needed but data isn't immediately lost.

Level 3: Database Replication

Replication maintains synchronized copies of the database on different servers:

Synchronous Replication:

• Transactions don't commit until confirmed on replica
• Zero data loss guarantee (RPO = 0)
• Performance cost (latency added to commits)
• Replica can take over immediately if primary fails

Asynchronous Replication:

• Primary commits without waiting for replica
• Small potential data loss (recent commits may not be replicated)
• Better performance than synchronous
• Replica may lag behind primary

Level 4: Geographic Distribution

For disaster protection, copies must be geographically separated:

• Different buildings (fire/flood protection)
• Different cities (regional disaster protection)
• Different countries (geopolitical risk, extreme disasters)

Backups are the ultimate insurance against media failure. While RAID and replication provide real-time protection, backups provide point-in-time copies that survive even catastrophic events.

Backup Types:

Backup Strategy Design:

A well-designed backup strategy balances:

1. Recovery Point Objective (RPO): Maximum acceptable data loss

   • RPO = 1 hour means you can lose up to 1 hour of data
   • Requires log backups at least hourly

2. Recovery Time Objective (RTO): Maximum acceptable downtime

   • RTO = 4 hours means database must be restored within 4 hours
   • Affects backup method, storage speed, and procedure complexity

3. Storage Costs: Backup storage capacity and performance

   • More frequent backups = more storage
   • Faster storage = faster recovery but higher cost

4. Operational Overhead: Time and resources for backup management

   • Backup verification
   • Rotation and retention
   • Off-site transfer

Media failure recovery in production environments involves considerations beyond the technical recovery process:

The Human Factor in Media Failures:

A significant percentage of media failures involve human error or can be prevented/exacerbated by human actions:

• Accidental deletion is effectively a 'logical' media failure
• Misconfigured RAID may not provide expected protection
• Untested backup procedures fail when needed
• Ignored warning signs (SMART alerts, replication lag) precede many failures
• Poor documentation extends recovery time

Monitoring and Alerting:

Proactive monitoring can catch problems before they become failures:

• SMART monitoring for disk health
• RAID controller alerts for degraded arrays
• Replication lag monitoring
• Backup job monitoring
• Storage capacity alerts
• Log archive monitoring