Risks of Switching Between Simple & Full Recovery in SQL Server 2005 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:


Am I totally ignorant to think that switching back and forth from Simple to Full is a bad idea on a regular basis?
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
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).