T-Log backup question

Question

T-Log backup question

DBAhawk

SSC Enthusiast

Points: 187
More actions
December 28, 2007 at 8:10 am

#180097

I'm looking for a little clarification transaction log backups. Backup/restore is my weakest area in database administration, which is unfortunate as it is arguably the most important.
Here's the situation:
We have an application/database supplied by Vendor. The database was initally set up with full backup at midnight and log backup at noon each day (recovery model = Full). A couple times in the past 60 days or so we've experienced excessive growth of the log file, to the point that it ran out of space (after ballooning to multiple GB). The database is less than 100 MB, so it doesn't seem right for the log to grow this wildly, although there are a lot of reads and writes - the application manages security for electronic door locks at several locations.
The vendor's solution to our problem was to enable the option "truncate log on checkpoint". However, after changing this option to True our log backups began failing. Here's what I did: I set Truncate Log on Checkpoint back to False, and increased the frequency of log backups (every two hours from 7 am to 7pm, the period of highest activity). The log is currently at 18 MB, but during one episode swelled up to over 100 GB (no, that's not a typo).
My question is twofold.
1) What are the technical details behind the log backup failure when the Truncate option was set to True?
and 2) Does the current setup seem likely to help prevent the problem from returning?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Setting the DB to Truncate Log on Checkpoint means you've switched the recovery mode to Simple.

In Simple recovery mode, the inactive portions of the log are discarded once the data has been written to disk (during a checkpoint)

The point of transaction log backups is to allow point-in-time restores of the database in case of failure. You would restore the full backup, the differential (if applicable) and then the transaction log backups up to the point where the failure occurred. That way, you'll have lost minimal to no data in the case of a disaster.

Since, in simple recovery mode, the log portions are discarded, there's no way to get a chain of log backups for the purposes of restores. You cannot back up the log of a database in simple recovery mode. There's no point (since you won't be able to use the backups for anything)

If you don't need to be able to recover up to the moment of a failure, then go with the vendor's suggestion. If you do need, then switch back to full recovery mode, shorten the interval between log backups, and give the vendor a good klap upside the head for a short-sighted solution.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DBAhawk SSC Enthusiast Points: 187 More actions · Answer 2

Thanks. Sounds like I did the right thing, then. If the problem recurs, I'll shorten the log backup interval again perhaps first adding additional backups in the off hours in case I misjudged the amount of activity outside business hours. So is the Truncate option functionally equivalent to the simple/full flag? Or are there other nuances that I should know about?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

DBAhawk (12/28/2007)
Sounds like I did the right thing, then. If the problem recurs, I'll shorten the log backup interval again perhaps first adding additional backups in the off hours in case I misjudged the amount of activity outside business hours.

And maybe investigate what's happening to make the log grow so fast

So is the Truncate option functionally equivalent to the simple/full flag? Or are there other nuances that I should know about?

As far as I know, yes. There may be nuances that I don't know about. I'm not an administrator myself. Check books online, see what that has to say.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

I would recommend that you do transaction log backups much more often; every 15 minutes 24x7 is a good starting point. If your transaction volume is really high, you might want to shorten it to every 5 minutes. This will help to keep the transaction log files from growing so explosively. It will also help you to identify the times when the activity in you database is really high by looking at the size of the transaction log backup files.

Since your database is fairly small, it would be good to do additional full backups during the day to cut down the amount of time it would take to recover your database to a point in time. You can avoid having to restore multi-gigabytes of transaction logs if you have to do a recovery. Since your database is only 100 MB, you should be able to do a backup in just a few seconds, so doing a backup every hour during the busy part of the day would be a good starting point.

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 5

No differences between Truncate on checkpoint and simple I know of. The former is older syntax.

I agree with Michael, I'd run more frequent log backups and then add in another full or at least a diff during the day, maybe every 4 hours. Check sizes to see when you're the busiest.