Eight terabytes, flat memory, and a bug that reported live files as deleted

A case study — Preservation Hold Library reporting

Summary

| | |
|---|---|
| **Problem** | Nobody could say what was in an 8 TB SharePoint Preservation Hold Library — what was retained, what had been deleted from the live site, or by whom. |
| **Constraint** | At 8 TB nothing finishes in a browser, memory cannot grow with the data, and a multi-hour run that dies must not start over. |
| **Solution** | A .NET 8 console app that streams the library by an indexed ID cursor, accumulates aggregates only, and checkpoints as it goes — paired with an SPFx web part for the sizes it can handle. |
| **Outcome** | Working. Two genuine defects found and fixed along the way, one of them a correctness bug that misreported live files as deleted. No tests. |
| **Role** | Full implementation — console app, SPFx companion, reporting, diagnosis of both defects. |
| **Stack** | .NET 8 · PnP.Core 1.14 · ClosedXML · SPFx / TypeScript |

1. The problem

The Preservation Hold Library is where SharePoint keeps content that retention policy says cannot be destroyed, including content a user has "deleted" from the live site. It is invisible to normal users, it grows continuously, and it is the first thing a records manager or an auditor asks about.

At 8 TB, the ordinary answers stop working. The library view will not open. A script that reads the items runs out of memory. And the question is not simple: *what is in here, what has actually been deleted from the live site, how much is it, and who deleted it?*

2. The constraints

Three, and each one eliminates an obvious approach:

A browser cannot finish. An SPFx web part is the natural fit for a SharePoint report and simply will not complete at this size. The README states the split without apology: *"Use Stats when SPFx can't finish in the browser at 8 TB."* Two tools, one for each size class, rather than one that fails at the top end.

Memory cannot scale with data. Anything that materialises the item collection dies. The processing has to be streaming, with flat memory regardless of library size.

A multi-hour run will be interrupted. Over that duration, something will go wrong — a token expiring, a network blip, a laptop sleeping. Restarting from zero is not a recovery strategy.

3. The design

Stream by an indexed ID cursor. Rather than paging by offset — which degrades and eventually hits the list view threshold — the scan walks the library by item ID, which is indexed by definition. That is both threshold-safe and naturally resumable: the cursor *is* the progress marker.

Accumulate aggregates only. Nothing per-item is retained. Memory stays flat whether the library holds ten thousand items or ten million.

Checkpoint every N pages, so an interrupted 8 TB run continues from where it stopped rather than restarting.

Two output paths: a complete CSV, and an Excel workbook whose detail sheet is capped — because ClosedXML holds the whole workbook in memory, so an uncapped Excel export would reintroduce exactly the problem the streaming design exists to avoid.

4. The two bugs

Both are worth telling, and they are different species.

4.1 A library that accumulates behind your back

PnP Core's list.Items collection accumulates across paged calls. Every scan loop re-iterated all previously loaded items — O(n²) — and held them in memory. Fatal on an 8 TB library.

This is the most dangerous kind of performance bug: the code looks correct. The loop pages properly, processes a page, moves on. What is invisible at the call site is that the client library is quietly appending each page to a collection that is never cleared — so iteration N walks all N pages, and the collection grows without bound.

At small scale it is imperceptible. At 8 TB it is fatal, and the symptom — slow, then out of memory — points at your own loop rather than at the library's collection semantics.

The general lesson: a client library's collection may have accumulation semantics you did not choose. On a streaming workload, verify what the SDK is holding, not just what your code holds.

4.2 Double-unescaping, and everything under a folder with a space

The report's central comparison is between the preserved copy and the live site: does this file still exist, or has it been deleted? That comparison is a URL match, so URL canonicalisation *is* the correctness of the report.

The normalisation routine unescaped the URL and then took Uri.AbsolutePath — which re-escapes spaces. The result was a string escaped once more than the one it was compared against.

Every file under a folder whose name contains a space was reported as DELETED.

The report ran, produced plausible output, and was wrong in a way that would have caused real harm: a records manager reading it would conclude that content had been destroyed when it was sitting in the live site untouched. The failure is systematic (every file under such a folder), silent, and invisible unless someone spot-checks against reality.

Two round trips through an escaping function is a classic defect, and this is a good demonstration of why it matters more in a comparison than in a display: a wrongly-escaped URL shown on screen looks slightly odd; a wrongly-escaped URL used as a join key changes the answer.

5. An honest limit

The report shows who deleted each file, and the documentation is precise about what that actually means:

"Deleted by" uses the preserved copy's Editor — SharePoint exposes no true deleted-by outside the audit log.

The value is the best available proxy, not the fact. Labelling it as a proxy in the documentation is the difference between a useful report and one that will eventually be quoted in a dispute as though it were authoritative.

6. Outcome

Working, at the scale it was built for. A resumed run correctly warns that change-type counts cover only the re-scanned portion — a partial-result honesty that matches the design elsewhere in this portfolio.

What is missing: no tests, anywhere. For a tool whose output could inform a retention or legal question, and which has already had one silent correctness defect, that is the most significant gap. The two bugs above were found by running it against real data at real scale; a test suite would have caught the URL one immediately and would never have caught the memory one.

7. What I'd take from this

Match the tool to the size class rather than building one that fails at the top. The SPFx web part is right for most libraries and cannot finish at 8 TB. Building the console app as a peer, and saying plainly in the README when to reach for it, is better than one tool with a size limit nobody documents.

In a comparison, canonicalisation is correctness. The URL bug was not cosmetic. It changed the answer, systematically, for a whole class of paths — and it produced a report that looked entirely reasonable.

Verify what the SDK holds. Streaming code with flat memory can be defeated by a client library that accumulates behind the call you are making. The code was right; the assumption about the library was not.

Name your proxies. "Deleted by" is an Editor field standing in for something SharePoint does not expose. Reports get quoted long after their author has moved on, and the caveat has to travel with the number.