Okay, I'm not a server administrator, a network guy, or a DBA. I'm merely a programmer helping out a small company. They have IT guy who isn't MS centric (most stuff is on Mac) and he and I are trying to figure out a solution here.
We've got 1 main database. We run nightly full backups. I know they are full backups because I can take the latest file, or any of the daily backups, and go to a completely new machine and "restore" the backup to an empty database and our app runs perfectly fine off of this backup.
The backups have grown from 60 MB to 250MB over 4 months. When running, then log file is 1.7 GB, and the data file is only 200-300 MB. Yes, recovery model is set to full.
So, my question, after all of that, if we are keeping daily backups, and we don't have the need / aren't smart enough to roll the DB back to a certain time, if I change the recovery mode to simple, am I really losing anything?
And, if I do change it to simple, will it completely dump the log file or at least reduce it way the hell down?
And, will that make our database run faster? I know that it'll make my life easier when I copy a relatively recent backup to my local machine to do development and testing...
Yes, changing the recovery model to simple should get rid of the logfile.
However, you might want to consider just setting up a backup plan for the logfile. This gets you a (typically nightly) full backup, and periodic backups of the transaction log. This keeps the size of the active transaction log to a more manageable size, and preserves the ability to do a point-in-time restore.
All you need is a set of 3 maintenance plans: one to do a full backup, one to do a transaction log backup, and one to do a maintenance cleanup task to clean up old backups (those beyond your retention threshold).
Have you made sure that the log file is being truncated? It should be by default.
If you are saying that a simple backup is ok because you are doing daily backups you could try to issue a truncat log statement and then take a full backup so as to keep the full backup log. There are some links dealing with truncation on the net but here is a quick one:
http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/