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?



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.

  • +1 Just wanted to note that since there are no log backups taken before or after the truncate that the log files are actually rendered totally useless here. The best option is absolutely to start doing log backups but db2's answer is not a bad workaround considering you seem to have no choice. – Sean Howat Mar 18 '11 at 17:52

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.

  • I guess one caveat is that the recovery logs would be useful if something, say, dropped all tables in the DB but the system (and the transaction logs) was intact; then you could roll forward to right before the issue. But, this is correct.. if you're doing transaction logs, you should be backing them up frequently then truncating them right after the full backup. – Shane Madden Mar 18 '11 at 16:49
  • @Shane Madden: That's exactly what I had in mind: the log file is on a different partition than the data files, so my idea is, if something happens to the data files, I could recover the full backup, and then restore to a more recent point in time using the log. That's why I'm not sure the Simple recovery mode is the best solution. But the plan doesn't work if the log file is "invalidated"... Thanks. – MacThePenguin Mar 18 '11 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.