At 14:07 on a Tuesday the order database stopped accepting writes for forty-one seconds. No blocking, no failover, nothing in the error log beyond a quiet line at the end: the data file had filled, autogrowth was set to ten percent because someone chose that years ago, and SQL Server zeroed all 32 gigabytes of new space before resuming the suspended write queue. Nothing was broken. The database was simply doing the most expensive chore in its repertoire, unscheduled, at peak load.
Autogrowth is a safety net that most estates quietly use as a sizing strategy, and the bill arrives as write freezes. Here is what actually happens during a growth event, what Instant File Initialization changes, and the pre-sizing discipline that makes the whole topic boring on SQL Server 2016 through 2022.
What actually happens when a data file grows?
When a data file fills, the allocations that land on it wait while the engine extends the file by the autogrowth increment — a filegroup with several files can keep allocating from the others' free space, but the file that triggered growth still stalls its share — and by default the new space must be physically zeroed before use, because SQL Server refuses to let a database page hand back whatever bytes the previous tenant of that disk sector left behind. Writes to the file stall for the duration, and a 32 GB zero-fill at real disk speeds is tens of seconds of write freeze. The transaction log pays this price almost every time: log files are zeroed on every growth, because crash recovery reads the zeroed tail to determine where valid log records end — SQL Server 2022 carves out one exception, letting log growths of 64 MB or less initialize instantly, but anything larger still zero-fills, privilege or no privilege. That asymmetry — data files can skip the zero-fill, log files almost never can — is the single most misunderstood fact in this corner of the product.
Why is percent growth a compounding trap?
Ten percent was the product default for years, and percentages compound. Ten percent of a 10 GB file schedules a harmless 1 GB growth; the same setting on a 600 GB file schedules 60 GB growth events, each one a freeze, each one larger than the last, each one landing later in the business day as the file outgrows the off-peak window. The fix is a fixed megabyte increment sized against your disk's zero-fill speed — or against a few seconds of allocation headroom once instant initialization is on. I typically land between 512 MB and 4 GB for data files depending on volume, and I always check what the current setting actually is, because the answer is almost never what the runbook claims.
What does Instant File Initialization change?
Instant File Initialization skips the zero-fill for data files. When the service account holds the Perform volume maintenance tasks privilege — SE_MANAGE_VOLUME_NAME — SQL Server claims disk space instantly and growth events that took tens of seconds complete in milliseconds, with one exception worth writing on the wall: on a TDE-enabled database, data-file growth is still zeroed, privilege or not. Since SQL Server 2016 the installer offers a checkbox granting the privilege at setup; on older builds it is a Local Security Policy assignment plus a service restart. Verify it rather than assuming: sys.dm_server_services exposes instant_file_initialization_enabled from 2016 SP1 onward — on the rare RTM build, the trace flags below are the proof — and migrations to new service accounts silently drop the privilege all the time. Trace flags 3004 and 3605 together write zeroing activity to the error log, which is how you prove on a live system whether growths are zeroing or not.
The honest tradeoff: without zeroing, a freshly grown extent can contain bytes from previously deleted files, readable by anyone able to examine raw database pages until those pages are overwritten. With TDE enabled the concern mostly evaporates, and most security teams sign off — but have that conversation explicitly instead of discovering it in an audit.
Why does the log almost never use IFI, and what follows from that?
Crash recovery depends on finding zeros where the log ends; a log file full of stale sector garbage would make the end of the valid log ambiguous, so the engine zeroes every log growth — the one carve-out being SQL Server 2022's instant initialization for growths of 64 MB or less, which helps exactly the too-small increments you should be eliminating anyway. Two consequences follow. Log autogrowth increments deserve the same fixed-size discipline with extra stinginess, because any event past the small 2022 carve-out is a write freeze with no IFI escape hatch. And the log deserves deliberate pre-sizing most of all: grow it once, at a quiet hour, to the size the workload's largest transactions plus margin need. The VLF layout those manual growths produce is its own topic, covered in my VLF management notes. A log that grows itself in production is a log telling you it was never sized.
How do I find recent growth events?
The default trace records them: EventClass 92 for data file auto grow and 93 for the log, with Duration showing exactly how long each event took — the number that turns "autogrowth happens sometimes" into "autogrowth cost us forty-one seconds on Tuesday".
DECLARE @trace nvarchar(260);
SELECT @trace = path FROM sys.traces WHERE is_default = 1;
SELECT t.StartTime,
e.name AS event_name,
t.DatabaseName,
t.FileName,
t.Duration / 1000 AS duration_ms,
t.IntegerData * 8 AS growth_kb
FROM sys.fn_trace_gettable(@trace, DEFAULT) AS t
JOIN sys.trace_events AS e
ON e.trace_event_id = t.EventClass
WHERE t.EventClass IN (92, 93)
ORDER BY t.StartTime DESC;The default trace rolls over, so for a longer history run an Extended Events session on sqlserver.database_file_size_change, available from 2016 onward. Duration is the column that matters. Growths completing in single-digit milliseconds are the signature of IFI doing its job and growths measured in seconds smell like zeroing — duration alone is not proof, since a small zero-fill on fast storage can also be quick, but combined with the IFI flag and the trace-flag output above it is usually case closed. And when the trace shows growths every few minutes at a 1 MB increment — the ancient 1 MB data and ten percent log defaults still lurk in the wild — the cost is not one big freeze but death by a thousand cuts: filesystem fragmentation and constant allocation-map traffic.
How do PFS and GAM make tiny growths worse?
Every data file carries allocation metadata on a fixed cadence: GAM and SGAM pages early in the file map extent allocation, and a PFS page every 8,088 pages tracks page-level fullness. Tiny, frequent growths mean the engine is constantly extending and revisiting those maps under latch, and each new increment lands wherever the volume has room, so the file ends up physically fragmented into thousands of small extents. A file pre-grown once to its working size has contiguous space and boring allocation-map traffic; a file that has autogrown forty thousand times by a megabyte has neither. This is also why "shrink it back down" is never the right response to having over-grown: shrink re-fragments everything the careful sizing bought, and the file grows again next month anyway.
What does a sizing discipline look like?
Mine is four lines long. Pre-size data and log files to the forecast for the next twelve months, at a quiet hour, using the growth history as the forecast. Set autogrowth to a fixed increment that completes in a couple of seconds — it is the safety net, not the sizing strategy. Enable IFI everywhere the security review allows, and re-verify the flag after every service account change. And alert on growth events themselves, not just on free space: free space tells you a growth will happen, the event tells you one already hurt. The goal is that autogrowth, when it fires, is a surprise you investigate rather than a scheduler you rely on.
File health with MonPG when SQL Server support ships
The telemetry that matters: growth event counts and durations per file, data-file free space trend, and the IFI flag — all boring, all decisive at 14:07 on a Tuesday. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and the SQL Server monitoring (coming soon) page carries the honest status. Until it lands, the default trace query above run weekly is the whole monitoring stack — and forty-one-second freezes become things you read about in other people's postmortems.