{"id":89,"date":"2008-07-23T22:55:16","date_gmt":"2008-07-23T20:55:16","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=89"},"modified":"2008-07-23T22:55:16","modified_gmt":"2008-07-23T20:55:16","slug":"recovery-model-and-transaction-logs","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2008\/07\/23\/recovery-model-and-transaction-logs\/","title":{"rendered":"Recovery model and transaction logs"},"content":{"rendered":"

Or “Remind me again why we’re doing these backups in the first place<\/em>”<\/p>\n

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.<\/p>\n

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.<\/p>\n

Recovery modes<\/strong><\/h3>\n

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.<\/p>\n

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.<\/p>\n

Simple recovery mode<\/strong><\/p>\n

In simple recovery mode, once a checkpoint completes, inactive log records are discarded. Hence no record is kept of transactions that occurred before the last checkpoint<\/p>\n

Full recovery mode<\/strong><\/p>\n

In full recovery mode, log records are retained within the transaction log until a log backup occurs. When a log backup occurs, SQL checks when the last log backup occurred, and writes out log entries since that time into the log backup. Then it updates the last log backup entry and discards the transaction log entries that were included in the backup. Hence, provided the log backup files are kept, there is a complete records of all transactions that occurred.<\/p>\n

That’s a massive simplification, but it should be enough for now. Second bit of background information is on the types of backups, what they do to the transaction log and the implications they have for recovery<\/p>\n

Backup Types<\/strong><\/h3>\n

Full Backup<\/strong><\/p>\n

The full database backup copies the entire contents of the database to the backup file. The full backup also includes enough of the transaction log to allow for the backup to be restored into a transactionally consistent state. It does not truncate the transaction log.<\/p>\n

Transaction Log Backup<\/strong><\/p>\n

The transaction log backup does not contain contents of the database, but rather just the transaction log. When the log backup runs, it backups all entries in the transaction log that were not backed up by the previous log backup. Once completed, the inactive log records are discarded, freeing up space in the log.<\/p>\n

Transaction log backups form a chain, starting with the a full or differential backup and continuing until the most recent log backup. Because full backups do not truncate the transaction log, they do not break the log chain. An unbroken log chain is essential to restoring to a point in time.<\/p>\n

So with all that out of the way, I can finally come to my point.<\/p>\n

If a database is in full recovery mode and transaction log backups are being done, the reason for all those backups is to allow the database to be recovered to a point in time in the case of a failure. Truncating the transaction log and discarding log records is contrary to that goal.<\/p>\n

If a database is in full recovery mode just because that’s the default, no log backups are being done and point in time recovery is not required, then truncating the transaction log is just an interim fix and the log will get full again sometime in the future. In that situation, rather set the database into simple recovery mode where the log will truncate itself.<\/p>\n

If a database is in full recovery mode because that’s the default, no log backups are being done and point in time recovery is required, then set up the appropriate log backups so that point in time recovery is possible.<\/p>\n

Truncating the transaction log should be a last resort, not the first thing suggested.<\/p>\n","protected":false},"excerpt":{"rendered":"

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… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[20,15],"tags":[],"class_list":["post-89","post","type-post","status-publish","format-standard","hentry","category-admin","category-sql-server"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-1r","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/89","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=89"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/89\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=89"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=89"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=89"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}