Ok, so we have several instances on several nodes in a clustered environment. One of them is for JD Edwards stuff and has had an issue lately with a job failing because there was not space in the tempdb to complete it. The code for this job hasn't changed in YEARS, and yet I realize the data size may have.

Anyway, after setting the tempdb to some Autogrowth that wouldn't bork that drive I started digging deeper on this install. It seems there are some scheduled jobs that are switching the recovery mode back and forth from Simple to Full with backups supposed to be happening around those for some reason. I believe this is what caused the T-Logs backup to fail at some point and thus not have enough room for the tempdb itself to complete these long-standing JDE jobs.

So questions:

  1. Am I totally ignorant to think that switching back and forth from Simple to Full is a bad idea on a regular basis?
  2. If I am right that this is a bad idea what other scenarios other than the one I have already described can I use to illustrate this to my Sr. DBA who must have created this situation?



Yes, it is a bad idea.

Log backups relate to a full/diff backup at some point (and sequentially to each other). Any change in recovery model will invalidate the restore sequence.

So for this sequence you can only restore as far as step 2. Log backups from step 4 on are useless.

  1. Full
  2. Log
  3. Change recovery model
  4. Log
  5. Log

For everything you ever needed to know, read Paul Randal's backup/restore section

  • Not quite right. Changing from FULL to SIMPLE at Step 3 above will, in fact, cause the break in the log chain that you speak of. However, changing from FULL to BULK LOGGED will NOT break the log chain and the logs at Steps 4 and 5 would still be useful. They just won't be useful for "Point-In-Time" recoveries. Any log file backup created that includes any time spent in BULK LOGGED recovery model (even if it's just for milliseconds) will have to be used in its entirety during a restore. – Jeff Moden Dec 31 '12 at 0:44

In this situation you should first investigate what amount of data loss is possible-- i.e., whether you need point in time recovery at all times.

If you DO need to be able to restore up to the minute, then breaking the log chain by switching to the simple recovery model is not acceptable. You lose the ability for up to the minute restore after the final log backup is taken prior to switching to simple, and you do not regain it until you have switched back to FULL and then performed an additional log backup.

If you do NOT need to be able to restore up to the minute, you can investigate using the BULK LOGGED recovery model as a better alternative than switching to simple. Do note that you will still have a large transaction log backup after completing BULK LOGGED transactions, however.

Here is a helpful MSDN reference on switching from the FULL recovery model to either BULK LOGGED or SIMPLE.

From the context of your question, it sounds that your database log files are on the same drive as your tempdb files. If it is possible to isolate your TempDb files to another drive, that would be beneficial for management purposes, as well as performance (depending on your drive layout).

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.