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.
Recovery modes
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.
Simple recovery mode
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
Full recovery mode
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.
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
Backup Types
Full Backup
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.
Transaction Log Backup
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.
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.
So with all that out of the way, I can finally come to my point.
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.
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.
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.
Truncating the transaction log should be a last resort, not the first thing suggested.
Hello…
From what I have read here, it appears that doing a Log backup automatically truncates the log. As long as you have the members of the “chain” (that is, the proper log backups). point in time restores are possible. I don’t understand why you state not to do the log truncation.
What I’m saying is to not do the truncation without the backup, that is, don’t run
BACKUP LOG <DB Name> WITH TRUNCATE_ONLY
That discards log entries without backing them up, hence breaking the log chain.
For how long should the transaction log backups be saved on the disk so that disk space can also be saved ie. after a full backup and subsequent Transaction log backups and then a full backup, does the TLog backup become irrelevant because a Full backup has occurred now.
I’d retain for 2 full backups, just incase you go to restore the latest full and it’s corrupt. That way you can always use the older full and all the log backups since then.
It basically boils down to database recovery. The longer the backups are retained, the further back you can restore if needed for deleted data or due to newer backups being damaged.
Thank You Gail.. Your advice is much help..!!
My DB size is 400 MB and mirroring has been setup and log file has grown to 7 GB.
That means if I create two DB Maint jobs, one for full backup everyday since DB size is not very large and another for log backup every 2 hours and retain each Full backup for last 5 times and Transaction log backup for last 20 times.. would that be enough for a point in time restore.
Sounds reasonable.
If the log’s getting to 7GB, it may be worth reducing the interval between the log backups, should keep it smaller.
Thanks for Your reply Gail..
I created two jobs. One for Full Backup everyday and another for Tlog Backup every 2 hours.
Gail, can you please help me understand the concept of TLog Backups.
Like, if we have a verified full backup that can be restored, we are sure that data can be recovered.
Then we are also doing TLog backups.
But if we take a Full Backup, that has all the transactions upto that time, does that mean we can delete .trn files that were created earlier than that full backup.
And Wishing You and the team a Very Happy New Year..
Here is my scenario. The recovery mode is set to full. I do a full backup every night. I also do a nightly backup of the transaction logs and I truncate the file. I would like to shrink the log file because it is getting way too big. Is this backup scenario enough to recover the database if I need too? When I shrink the LDF file, will it have a huge impact on the performance of the database? And finally, am I correct is thinking that only the mdf file is required for the database to run and if I shrink the LDF file down to nothing, the DB will still work and continue sending logs to the same LDF file?
No, you are not correct in thinking that. The log file is a critical component of the database.
Shrinking the log will just make it grow again, and that’s a slow operation.
Why a log backup nightly? That’s resulting in your log being huge and means that you don’t have the ability to restore to any point of failure. If you need to be able to restore to any point in time, you need more frequent log backups (interval between log backups should be max amount of data you’re willing to lose in a disaster)
If you don’t need point in time restore, switch to simple recovery model.
http://www.sqlservercentral.com/articles/64582/