Or “Remind me again why we’re doing these backups in the first place”
If there’s one topic that comes up again and again on the forums, it has to be the problem of full transaction logs, usually followed by the discovery that the DB is in full recovery mode and no log backups are running.
Quite often, someone will suggest to just truncate the log and shrink it. It’s a dangerous suggestion, not so much for what is said, but for what is not said. To understand why, requires a little background information. First, a look at recovery models and how they affect the transaction log.
I’m going to ignore bulk-logged mode for now, mainly it’s less used than the other two and I’m not completely comfortable with how it works. I’m also going to ignore replication and database mirroring, as they complicate the issue.
Regardless of the recovery model that the database is in, transactions are logged into the transaction log before they are considered complete. The entries in the transaction log are considered active until all the data pages that were modified by the transaction have been written to disk. The two processes that write pages to disk are the lazy writer and the checkpoint process. Once all the pages that the transaction have been written to disk, the log records for that transaction are marked as inactive.