Why is it so important to backup your transaction log?

Question

We're currently implementing a backup solution for a client and their ERP solution uses SQL Server.

The ERP solution was set up by a different company. And they are telling me that it is super important to back up and truncate the transaction log.

I've been reading up a bit on this transaction log and I don't get why this is so important when I'm already backing up the whole machine anyway (We're using ArcServe UDP, which is aware of SQL Server and uses VSS). It is my understanding that cleanup tasks on the SQL Server VM are already taking care of truncating the log, however, UDP also allows SQL Server log truncation.

It is my understanding that the transaction log can be used to restore corrupted databases, because, well, it's a log of all transactions. But I already have an hourly backup of the whole database, so, why would I care?

Answer 1

You only have to do this if your DB Recovery Mode is set to "full". If it is set to "simple" you don't have to make a backup of the transaction log. But watch out for the difference between these two options!

First of all: If you want to be able to restore the DB to a specific point of time you have to use the "full" mode. (I think you can adjust the timing so accurate that you can even specify the milliseconds for the restore point) In "simple" mode you can only go back to the last full backup.

If you do not backup/truncate your transaction log, it will grow the whole time (in full mode). I saw databases where the .trn file was more than twice as big as the database itself. This depends on how often changes were made to the DB.

Another point is that a log backup is normally faster than a full backup.

So I think your backup plan to make a full backup every hour is not optimal. But it depends on your situation:

If you say: Okay if I can restore the DB to the last full hour, everything is alright. --> You can also think about setting the recovery mode to "simple" if you want to keep the full backup every hour.

In my opinion, a better idea would be to make a full backup in the early morning and then do a transaction log backup every hour. It should be much faster, and you are able to restore to any point of time you want to. And also your .trn file will not grow too much...

Hope this helps.

Answer 2

Well. You care because if you have your recovery model set to full and you don't back up the Transaction Log using SQL's backup (and not the server backup), the transaction log continues to grow until it consumes all available disk space. (I once saw a lesser colleague install SQL Server on the system drive and never back up the transaction log. It ate Windows.)

Yes, it will also restore to a specific point in time as well. Down to the minute. Like Twinkles says, yes, people dropping tables and the like.

I don't know what you're using for your hourly backup of the entire database, and if it's the same product as what you're using for the entire machine. If so, a non-SQL-aware backup solution is not supported for restores. The amount of time it takes for VSS to copy the MDF and LDF files can cause an internal timestamp mismatch, for example.

Answer 3

We manage several ERP systems as well. And the problem is often that at night there are often long running batch jobs which sync data with other systems. And they take sometimes an hour or more. So what you want to do in case of a crash is to jump to a point where you have consistent data. (Which means right between two batch jobs.) If you only look at the time you might not always know exactly what the status of the data base was at this time.

But of course it depends on the situation. If you don't have any automated jobs etc. you can be totally fine with an hourly backup.

Answer 4

There are several reasons why you want to do that:

1. A database system is usually busy, maybe doing thousands of transactions per second. The data could be spread out over several files on different filesystems. It is not trivial to make sure that the database is in a consistent (a.k.a usable) state after restoring. If your backup solution is up to the task, great, but you better be sure about this before betting your job on it.
2. An example: Somebody drops a table with important data by mistake. If you have a database backup with point-in-time recovery ability, you can restore the data quickly, without having to restore the whole system.
3. If the database is in full recovery mode, the transaction log of SQL Server will grow. Storage space in the transaction log is only reused if the transaction log has been backed up. If you do not back up the transaction log regularly, your file system will fill up until there is no space left. At which point everything will come to an immediate halt, since no new transactions can be started.

Answer 5

When your database grows beyond what you're able to backup in an hour, you need a different model.

A Full backup of your database will truncate your logs, but it needs to be "SQL aware", because in that scenario, it's the backup software that's telling SQL server what it has backed up, and what to truncate.

As others mention, if you have a database in the "Full" recovery model, it's transaction log will grow indefinitely, until you make a Full SQL-aware backup.

Recovery is really the issue here, not Backup. And it's not a technical decision, its a business decicion!

If your business owners are OK with losing an hour or more of their database transactions (which may be VERY difficult or impossible to redo!) then your model works. If they are OK with the system being down for hours while you restore the whole database from backup, then your model works.

However, if your business regards their ERP system as a critical asset for their operation (don't they all?), then setting a maximum acceptable recovery time (aka RTO, Recovery Time Objective) for your critical services will be a business decision.

Also, the business owners or system stakeholders need to define how much data they are willing to risk losing in an incident, aka RPO (Recovery Point Objective).

The answer if you ask them might be "NO data can be lost! The ERP system must be available 24/7/365!"... which we all know is highly unlikely to be cost-effective. If you present them with the cost associated with building such a fully redundant, non-stop system, they will come up with more reasonable figure.. ;)

The point is, if you can avoid losing any transactions, you're saving your business potentially hundreds or thousands of lost work hours. It amounts to HUGE savings in any company, and grows with the size of your company...

Answer 6

Everyone had great responses to this, but I'd like to add another important note... or two.

Knowing the particulars of SQL Server recovery models and your business requirements for data loss are both very important; however, in this case it is imperative that you understand how your backup product works with SQL Server. (Based on the comments above, it sounds like you are backing up disk volumes via VSS copy, which means SQL Server backups may or may not be required in addition.)

Having recently evaluated a similar product, some of the important points you might need to ask about are:

• How are restores performed to a point in time for a database in full recovery?
• How is the initial backup handled for a new database in full recovery?
• Does the backup product require SQL Server log backups to restore to a point in time? (In my case, the answer was yes.)
• Can your storage infrastructure handle the volume of data for the VSS copies/differentials (at a given interval) in addition to the normal SQL load?

Hope this is helpful.

The experience my team had with our recent evaluation provided some very interesting answers to the above questions. One thing is for sure, backups are more complex for us with a VSS backup product.

Answer 7

As many others have already said, if you are using a third party tool to backup / snapshot either the VM or the storage, you still run a risk of not having a valid backup. All third party tools that manage SQL Server backups will implement and connect to SQL Server using VSS. It does this to request that SQL Server quiesce all I/O to the data files so a consistent snapshot can be taken. If not, then you can have many transactions in various states and a restore will not know if those transactions can be rolled forward or backward.

I have not worked with every third party VM / Storage snapshot tooling out there, but the ones I have worked with were never able to snapshot storage where System Databases were located - SQL Server cannot quiesce those databases. They ALL backed up those databases in a streamed manner - ie... issuing the BACKUP DATABASE commands and then snapping the backup file itself.

On top of all that, as many have also said, if you are in FULL recovery model, and you do not issue BACKUP LOG statements regularly, the transaction log will continue to grow until there is no room left on the disk.

The real question you need to be asked, and I might have missed it above... have you successfully restored from these backups a number of times, and are you happy with the consistency of the data in those restores. Personally, even that would not be enough for me, it still feels like a roll of the dice, and that's something a good DBA never takes when it comes to backup and recovery.

Answer 8

Recognize that transaction logs are not simply a recovery mechanism. Proper log maintenance can also play a critical role in overall database performance (i.e., transaction throughput).

Frequently backing up your log files does a couple of things:

1. It reduces the VLF count in the physical log files which is good for performance.
2. You're better prepared to use the log backups in the event that you need to recover a database.
3. It's quite a bit quicker than a full backup

If you can get away with doing a full backup hourly then you I'm not sure how much you would benefit from more frequent log backups. After all as I understand it a full backup will also backup as much of the log as is necessary in order to ensure a complete restore.

On the other hand, if your app generates tons of transactions in between your hourly full backups then that might explain why the original devs suggested more granular log maintenance. Lots of transactions could grow the VLF count in your logs which can incur a performance penalty until the log is truncated. I've seen this expressed as a 'query timeout expired' error within an application (shortly before it hangs).

Recommendations related to transaction log maintenance are described very well in this article 8 Steps to Better Transaction Log Throughput. Additionally, this article Top Tips for Effective Database Maintenance mentions a somewhat arbitrary VLF count to aim for (< 200) which has worked very well for me.

Answer 9

Other people have already given most of the reasons for a translog backup etc. There seems to be some doubt as to why this is good strategy when you already backup the server.

A couple of good reasons have come up for me that are not above. What if you 3rd party app fails to take a backup you can restore? Have you tried to restore your backup? What about to a new server you have just built from your templates (think DR)? What about to another server on your domain that has a different collation? or SQL instance?

I take redundant backups for no reason other than sometimes your third party app is not the fastest way to restore. Sometimes the storage your 3rd party app is saving to is affected too, or is corrupt for its own reasons.