How a single 9 GB upload ran our database out of disk (twice)

A couple of weeks ago, on our own test bench, we uploaded a 9 GB file over SFTP into a Stylus SFTP Server instance configured with the database storage backend. We watched the progress bar climb past 70%, past 80%, past 90%. At 92% it stopped. The SFTP client reported insufficient resources and aborted. The disk on the server was at 100%.

The interesting part isn't that this happened — large uploads run out of disk all the time. The interesting part is what was on the disk. It wasn't really the file. It was the database's record of how to undo writing the file.

This is the autopsy — how a 9 GB upload turned into roughly 18 GB of in-flight database state, why SIMPLE recovery didn't save us, what we did to recover, and what we changed afterwards. If you operate any system that streams large objects into a relational database under a single transaction, the same arithmetic applies to you.

How the database storage backend writes a file

Background, in case you missed our earlier post: Stylus SFTP Server lets you choose where uploaded files actually live. The default is the filesystem — same model as every other SFTP server — but you can also point it at a JDBC database, in which case file content goes into a chunked table (sftp_file_storage with companion rows in sftp_file_chunks) rather than a directory.

When the SFTP client streams bytes, the storage layer streams chunks into the database. Each chunk is an INSERT. The whole sequence happens inside a single open transaction, which commits only when the client cleanly closes the channel. That gives us the property we wanted: a row in the table either represents a fully-uploaded file, or it does not exist. There is no half-state visible to downstream consumers. If the client disconnects mid-upload, the transaction rolls back and the chunks are gone.

That atomicity guarantee is the entire point of putting files in a database. It's also the thing that ate our disk.

The math

Here is what was on the volume at the moment the upload stopped:

The data file is roughly the right size: 92% of 9 GB is about 8.3 GB, and the chunks that had been inserted live there. The eye-opener is the transaction log: 9.5 GB, larger than the data we'd actually written.

SQL Server, while a long-running transaction is open, has to keep every write recorded in the log. The redo records say "we inserted chunk N of file F." The log is the source of truth for both replaying the transaction (if it commits cleanly and a crash happens before the data pages are flushed) and rolling it back (if the transaction is aborted). Until the transaction either commits or rolls back, the log cannot be truncated, and the records cannot be discarded.

For a streaming upload, that means every chunk you write to the database adds roughly one chunk's worth of bytes to the log. The two numbers grow in lockstep. The log was bigger than the data because of overhead per record — LSN headers, page metadata, statement-level entries — but the order of magnitude is the same. If you push 9 GB of data into a single open transaction, you should expect roughly 9 GB of log.

Why SIMPLE recovery didn't help

The instinctive response, when transaction logs get big, is to switch the database to the SIMPLE recovery model. SIMPLE is supposed to keep logs small by auto-truncating after every committed transaction. The database was already in SIMPLE. That's why it never got worse — previous uploads didn't leave their logs behind — but it does nothing while a transaction is still open.

The rule is straightforward: log truncation can only reclaim space behind the oldest active transaction. While the upload is in flight, the upload is the oldest active transaction, and there is nothing behind it to truncate. SIMPLE, FULL, BULK_LOGGED — the recovery model determines what happens when the transaction closes, not what happens while it's open. Different setting, same problem.

The rollback gotcha

It gets worse. When the upload aborted at 92%, SQL Server had to roll the transaction back. That means writing compensation log records — CLRs — which are themselves log entries that describe the undo operation. Roughly speaking: every redo record gets one CLR's worth of additional log space when the transaction unwinds.

If our 8.2 GB of data created 9.5 GB of redo log, the rollback needed approximately another 9.5 GB of log space to record the undo. We didn't have it. The volume was already full. SQL Server stopped, logged a fatal error, and the database transitioned into an inconsistent state we couldn't unwind from without making space first.

The peak space you actually need, for a single open transaction the size of a file, is roughly:

peak ~= file_size               (data, in the .mdf)
      + file_size * 1.1         (redo log, while transaction is open)
      + file_size * 1.1         (CLR / undo log, if it rolls back)
      = ~3.2 x file_size

If you've been sizing transaction-log volumes based on average steady-state usage, that's the number that catches you out. For a 9 GB file, it's not 9 GB of database storage that you need to budget — it's closer to 30 GB during the upload window.

What we did to recover

The immediate problem was a database that wouldn't accept any new operations because its volume was full and it couldn't roll back its own state to free space. The fix was inelegant.

The first instinct was to truncate the sftp_file_storage and sftp_file_chunks tables to reclaim the rows and let the database move on. TRUNCATE on sftp_file_storage failed with the cleanest possible error message:

Cannot truncate table 'sftp_file_storage' because it is being referenced by a FOREIGN KEY constraint.

The chunks table holds an FK on the storage table. TRUNCATE doesn't fire row-level cascades; it requires the referenced columns to be unreferenced. So TRUNCATE was out. The two viable paths were:

• Drop the FK, truncate, recreate the FK.
• Drop the tables and let Stylus SFTP Server recreate them on next boot.

We took the second path. On the next server start, the schema check creates the tables fresh, with the FK still in place. The database came back clean, the volume had headroom, and we could move on. Nothing was lost — this was a test environment, and the file we were trying to upload was still in our local copy — but the procedure made it obvious that "schema is self-healing on boot" is a useful operational property to have, and one we hadn't really thought to advertise.

What we changed

The first question we asked was whether the storage layer should be doing periodic commits during a large upload — flush every N chunks, free the log, continue. Most "stream into database" systems work this way. It would have prevented the disk-full entirely.

We considered it carefully and decided against it. The atomicity guarantee is the reason we built the database backend in the first place. If chunks are visible to downstream consumers before the upload finishes, then a transaction-log-friendly partial write becomes a guaranteed source of half-files for any pipeline that polls the table. That's a worse failure mode than running out of disk — you can fix a disk-full incident in a few hours; you can't easily fix six months of partially-processed records that downstream systems already acted on.

What we shipped instead:

• VolumeGuard. A hard quota that checks available space on the database log volume against the inbound file size before accepting the upload. The check is conservative — it assumes roughly 3x the file size and refuses any upload that would push the volume over a configured headroom threshold. Soft warning at 80%, hard refusal at 95%. The client gets a clean rejection on session start, not a half-completed upload and a corrupt database. Configured in filesystem-config.xml; defaults to enabled when the storage backend is JDBC.
• Refined sizing guidance in the documentation — the 3x rule above, with worked examples for SQL Server and PostgreSQL. People who choose the database backend deserve to know the disk-arithmetic up front.
• Per-chunk compression (deferred). Filed as SSS-3 in our tracker. Adding a column to the chunk schema with a default of zero, switching the storage layer to compress chunks before insert when the column is set, and switching the read side to decompress transparently. For compressible content (text, code, structured data), this would shave 2-3x off both data and log usage. We chose to ship VolumeGuard first because it's the hard stop; compression is the optimisation that makes large files cheap, but it doesn't help if the customer hits a 50 GB upload and you didn't pre-reject it.

The lesson

Database storage backends inherit database properties. Almost every property you want — backup, replication, transactional consistency, downstream querying — is one of the database's properties. So is the way it allocates disk during long-running transactions. The same atomicity that makes a file in the database either fully there or fully not there is the reason 9 GB of file content can briefly occupy 27 GB of database state on the way in.

If you're sizing a database-backed file transfer system, the headline numbers to plan around are:

• Peak transaction-log size during an upload is approximately equal to file size.
• Rollback approximately doubles that peak.
• Recovery model only matters once the transaction closes; it does nothing while it's open.
• You want the log volume sized for at least 3x the largest single object the system can accept, or you want a volume guard that refuses uploads that would exceed it.

If you can't budget that headroom on your storage volume, the filesystem backend is still the right call for that workload — the underlying tradeoff is between operational properties and disk arithmetic, and neither answer is universally right. The database backend earns its keep when files are transactional and you want them to inherit the database's backup and replication story. It costs you a bigger volume during the upload window. Now you know how much.