Transaction log: Truncate vs. backup

Question

I have a SAP ECC instance which runs on SQL Server 2000. The database is set up with the Full recovery model, but a transaction log backup is never done (not my choice). Instead, a full backup is saved daily.

This of course implies that the transaction log file keeps growing. To control its size, once in a month the transaction log is truncated, and then shrunk with dbcc shrinkfile (again, not my choice).

As you can easily guess, this doesn't work very well because sometimes the log file grows too fast, fills its partition, and the database hangs. I was asked to make this situation better.

If the choice would be mine I'd do a transaction log backup multiple times per day, but the person that's actually in charge doesn't want that.

The second obvious choice would be to run the truncate & shrink job more often. But from what I understand it's not a good choice. One thing I've read here worries me the most:

by truncating the log at (time), you've just completely invalidated the consistency of your log and there's NO way to recover ANY data past (time of the last full backup).

Is this true? Does it mean the log file in use now is completely useless? If I started to regularly backup it now would it become usable for a point-in-time recovery? Would the log backups be consistent? Am I better off using the Simple recovery model?

Thanks.

Answer 1

That is mostly correct, if you truncate the log, then any log backups you attempt to make afterward are totally useless (I don't know if SQL Server will even let you make a log backup after that - it may very well not, simply because it would be useless). You have to take another full or differential backup to restart the log chain, at which point you can start doing log backups again.

And even if you never back up the log, it can still prove useful. If the volume hosting the data file dies, you can perform a log tail backup of the (presumably rather large) log, and use that very backup after restoring your full backup with NORECOVERY, and have little or no data loss. If the volume hosting the log file dies, however, you're mostly hosed without log backups.

Given the constraints you've specified (i.e. no regular log backups), I would continue with your current approach, but make sure a full backup is performed immediately after truncating. It's still a lousy way to operate, but it's safer than either using simple recovery or more frequent log truncations without full backups afterward.

But if you can convince the guys in charge, even a once-daily log backup would be a vast improvement. Maybe schedule it to happen immediately before the full backup.

Answer 2

You are better off using simple recovery model. a log file that is not backed up is not usefull anyway. ONLY backups can be restored. So, if you dont back up, waht good is the log?

Whoever decdied that should not work in IT, but that is not your pribme. WHen something appens, your company is history.