Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

TIA

share|improve this question
    
You instantly lose the ability for point in time recovery if you switch to simple and even when you switch back to full your database will be in auto truncate mode (of the logs) until the next full backup is taken. Presumably that's why the backups are being taken in the jobs. –  Martin Smith Apr 26 '11 at 17:19
add comment

migrated from stackoverflow.com Jan 21 '12 at 13:29

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 2 down vote accepted

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

share|improve this answer
    
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
add comment

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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