Editorials

Backups and Transaction Logs

by Ben Taylor


Top 10 Tips for Optimizing SQL Server Performance
SQL Server performance management requires serious time and energy. In this new white paper by Kevin Kline, discover detailed guidance on the issues you can relate to—from cache trashing to bad T-SQL that can return to haunt your applications. See how to maximize database scalability and efficiency with ease.
Read More


Backups and Transaction Logs
Two people have written in with responses to my Monday Editorial, “Manage Transaction Logs” regarding database backups and transaction log clearing. The newsletter encourages the Accidental DBA to focus on establishing a database backup strategy so that they don’t run out of disk space, primarily due to continuously expanding transaction log files.

Let me clarify some regarding transaction logs and clearing them to keep the file from growing. A full backup or differential backup will not clear the transaction log. This means that performing a full or differential backup simply gives you a new point from which you can start a database restoration. To clear the transaction log either the database must be in Simple recovery mode or you must perform a transaction log backup.

The purpose of a full backup is to reduce the time necessary to recover a database. In short, you restore from a full backup, and then apply all transaction logs occurring after that full backup (there is a little more involved).
So, if you did a full backup on Sunday and Wednesday, either full backup could be used to recover the database on Friday. If you used the Sunday full backup you would have to apply all transactional backups in order occurring after the Sunday full backup.

To reduce the recovery time, you could restore the Wednesday backup and apply all the transactional backups in order occurring after the Wednesday full backup. Either technique works. The difference is that it will take longer to recover from the Sunday backup.

Rather than providing all the details about how this works, let me point you to an editorial by Paul Randal, "Understanding SQL Server Backups", where he provides expert direction for you. My goal is to encourage the Accidental DBA to build a disaster recovery plan, and be aware that the transaction log (and the database files for that matter) have the capability to consume the entire disk on which they reside, and cause your database to stop working.

Many think that they solve the transaction log problem by simply setting their databases to Simple Recovery mode. What this means it that the transaction log is automatically cleared each time a checkpoint executes on the server. So, there are less issues dealing with transaction log backups. This thinking is true regarding the transaction log growth problem. However, it leaves you potential to have a greater amount of data loss because you can only restore from a full backup.

Thanks to Marty and Hruby for exposing the need to clarify this very important point.

Cheers,

Ben

$$SWYNK$$

Featured Article(s)
Tips for using SQL Server 2008 configuration options (Part 2)
In this article, you can find some useful tips to use SQL Server 2008 configuration options.

Featured White Paper(s)
Optimize SharePoint Storage with BLOB Externalization
Written by AvePoint

This document is intended to provide a comprehensive an… (read more)

Tags:
Author: Ben TaylorBen is a Relational Database veteran starting in the early eighties on DEC Vax database systems such as Bloom and Harvest. He has specialized in SQL engines for more than two decades, with a primary focus on Microsoft SQL Server. Ben has worked with small and large databases for both OLAP and OLTP working on systems for many industries such as Medical (Medical Claims and Eligibility Warehouse, HMO Provider Management, Enrollment), Financial (credit card processing, mortgage, stock portfolio management), Manufacturing (Assembly Line and Product Assemblies), Sales, Auto Auctions and more.