Using the full recovery model on a database properly
Solved Email & Outlook
MS
Michael Scofield
February 25, 2020
2 replies
10,640 views
Reviewed by moderators

Switched our main database to full recovery after a scare, and since then the log file grows without limit, currently ten times the data size, while everyone assures me full recovery is the professional choice.

What does running full recovery properly actually involve beyond flipping the setting?

Accepted Answer
Verified by Edwin J. Hoffer, Database Specialist ยท Reviewed February 2020

The assurances are right and incomplete: full recovery is the professional choice as half of a pair, the model plus log backups and the setting alone without its partner produces exactly your unlimited growth. The proper arrangement:

The mechanism first so the growth makes sense: under full recovery the log retains every transaction until a log backup captures it, that retention being precisely what makes point in time restore possible. No log backups means retain forever, the file at ten times data size being the model faithfully keeping its promise to a partner who never shows up. Simple recovery truncates on checkpoint instead, no log backups possible, no point in time restore, the honest model for databases whose loss tolerance is measured in whole backups.

The proper schedule, the pair completed: full backup nightly or weekly per size, differentials optionally between, log backups on a cadence equal to the answer to one business question, how much work can we afford to lose, fifteen minutes being the common answer and therefore the common log backup interval. The first log backup after the chain exists releases the retained log for reuse, the file stops growing and after a cycle or two you shrink the log once to its working size, the single legitimate shrink, sized to the largest interval's activity plus index maintenance headroom.

The disciplines that keep the pair honest: never break the chain casually, switching to simple and back invalidates every log backup since the last full and demands a fresh full immediately, verify restores by actually restoring, a backup unproven is a hope, monitoring on two signals, log backup job failures loudly since each failure extends both the loss window and the log growth and log_reuse_wait_desc in sys.databases whenever the log grows despite backups running, which names the real blocker, a forgotten transaction or an unread replication log rather than the model. Run the pair and the professional assurances become true in your instance rather than in general.

Log backups every fifteen minutes since Tuesday, the file released its space on the first one exactly as described, shrunk once to a sane working size and stable since. The log_reuse_wait_desc tip immediately caught a secondary issue too, an ancient open transaction from a monitoring tool. Fully professional at last, both halves.