Debugging a 7-Year 100% CPU Loop in iCloud for Windows (and Fixing It Live Inside SQLite)

Since around 2019, the iCloud Photos background process (iCloudPhotos.exe) on my Windows PC had been permanently running one processor core at 100%.

It was one of those everyday annoyances you eventually tune out. For seven years, it quietly slowed down the machine and kept the cooling fans spinning. Force-closing it in Task Manager never helped — Windows just restarted it a minute later and it went right back to 100%.

Yesterday I finally got tired of it, pointed an AI agent (Antigravity) at my machine, and asked: "Hey, can you figure out what iCloudPhotos.exe has been doing on that core since 2019?"

I didn't expect much since it's a closed-source app. Instead, in about 15 minutes — just by inspecting the app's diagnostic logs and its local SQLite database — the agent uncovered a chain reaction of six classic C++ and SQLite bugs, and built a live in-database fix that brought CPU usage down to 0% immediately.

You might think one CPU core is no big deal — until you imagine 100 million computers spinning one core nonstop for years on this exact problem, while photo sync is completely stuck behind a traffic jam.

Here is what was actually happening under the hood.

1. The Symptom: 70 MB of Logs a Day Repeating Every 4 Seconds

Inside the local iCloud package folder, iCloudPhotos.exe had accumulated 1.0 GB of plain-text logs (~70 MB/day) alongside a 1.75 GB SQLite database (client.db, ~118,000 rows). Every 4 seconds, a worker thread printed the exact same warning:

WARN WP::SyncDown::CreateOrUpdatePlaceholder WARNING: Asset has been deleted, but at an unknown bounced name.

Look closely at "Asset has been deleted" — there is a double space where the photo ID should be. The asset_id was NULL.

2. Bug #1: %lld vs %d Format Mismatch Writing 64-Bit Stack Garbage into Booleans

When inspecting the server_items table inside client.db, 70 rows had huge 64-bit numbers stored in their boolean columns (is_deleted and is_expunged), such as 2512555868161 (0x00000249_00000001).

In hex, the lower 32 bits are 0 or 1 (the actual boolean flag), while the upper 32 bits are leftover stack values. In SaveResourceAndMetadata.cpp, the CPLMaster UPDATE query formats is_deleted and is_expunged with %lld (64-bit integer) instead of %d in sqlite3_mprintf, pulling 32 bits of uninitialized stack state straight into SQLite.

3. Bug #2: The "WHERE asset_id = NULL" Infinite Loop

Every 4 seconds, WP::SyncDown::CreateOrUpdatePlaceholder queries server_items for the next batch of work:

SELECT master_id, asset_id, … FROM server_items WHERE ((placeholder_processed = 0) AND is_deleted = 0 AND asset_id IS NOT NULL AND master_id IS NOT NULL) OR (is_deleted = 1 AND master_id IS NOT NULL AND (is_deleted_locally IS NULL OR is_deleted_locally = 0)) LIMIT %lld

The first branch (is_deleted = 0) checks "AND asset_id IS NOT NULL", but the second branch (is_deleted = 1) forgets it. Because of that, 21 old deleted placeholder rows from 2019 with asset_id IS NULL always floated to the very top of every LIMIT batch.

After failing to find a local file for asset_id = NULL, the cleanup path tried to mark each row as processed:

UPDATE server_items SET is_deleted_locally = 1 WHERE asset_id = NULL

In SQL, "= NULL" always evaluates to UNKNOWN rather than TRUE (it has to be "IS NULL"). So that query updated 0 rows every single time. Four seconds later, the exact same 21 rows came right back at the head of the queue, starving 5,376 newer photos waiting behind them for seven years.

4. Bugs #3 and #4: Missing Indexes on a 1.75 GB Database

Why did a 4-second retry peg a full CPU core at 100%? Because server_items stores raw CloudKit blobs (1.75 GB total) and had no index on those queue flags — every retry forced a full 1.75 GB table scan (518 ms).

On top of that, the fingerprint_paths table (110,000 rows) had PRIMARY KEY(fingerprint, path) plus a duplicate index on (fingerprint), but no index on path — so every file path check scanned all 110,000 rows (30.6 ms per lookup instead of 0.027 ms).

5. Bugs #5 and #6: What Woke Up When the 7-Year Traffic Jam Cleared

Right after we marked those 21 stuck rows as processed, the 5,376 queued photos synced in two minutes and new records started streaming down from the cloud. Immediately, two more bottlenecks showed up in the logs:

First, ownership checks ran: SELECT owner_id FROM server_items WHERE '' IN (master_id, asset_id)

Putting the string constant on the left side of IN (master_id, asset_id) prevented SQLite from using B-Tree point lookups on either column, triggering another 1.75 GB scan (617 ms) per incoming photo.

Second, in SaveAssetRecordToDB, an UPDATE query using "WHERE (master_id = X AND asset_id IS NULL) OR asset_id = Y" matched both an orphaned placeholder row and an existing asset row at the same time, failing with SQLite[2067]: UNIQUE constraint failed.

6. Fixing Everything Live Inside SQLite

Without touching any application files, Antigravity fixed the entire chain directly inside client.db:

• Sanitized the 70 corrupted rows and marked the orphaned NULL placeholders as processed.
• Added AFTER INSERT/UPDATE triggers on server_items so SQLite automatically masks boolean flags (& 1) and cleans up duplicate NULL-asset placeholders on the fly.
• Added three covering indexes: queue queries dropped from 518.3 ms to 2.5 ms (205x faster), file path lookups dropped from 30.6 ms to 0.027 ms (1,110x faster), and owner checks dropped from 616.9 ms to 25.8 ms (24x faster).

CPU usage immediately dropped from 100% to 0%, the fans went completely silent, and all 5,376 stuck photos finally synced.

It's pretty wild that we're at the point where you can ask an agent "why is my fan loud?" and watch it diagnose and hot-patch a 7-year-old mystery over a cup of coffee.

(By the way, if anyone from Apple happens to read this — feel free to pass these notes over to the iCloud for Windows team!)