Home Forums SQL Server 2008 SQL Server 2008 - General *MAY* need to switch DBs from Full Recovery to Simple, due to backup space issues... RE: *MAY* need to switch DBs from Full Recovery to Simple, due to backup space issues...

  • Overall that sounds reasonable, given that you are in an emergency situation.

    About how long might it be before SQL auto-truncates the logs? Can I "force" it to happen sooner by issuing a "Checkpoint" on the DBs, and could it take more than one checkpoint?

    It shouldn't take long at all. Checkpoints automatically occur fairly frequently. I'd make the change to Simple ~20 mins before the full backups run, then switch back to full ~5 mins before (which probably won't really take affect until after the full backup anyway). That leaves only a very small vulnerability window.

    I'm not currently planning to shrink the physical files, so that SQL doesn't need to "grow" the file later. (As an example, one of the Log files is showing 18655.19MB used in the log... I had been monitoring the sizes of the DBs and TLogs for a couple weeks, looking to free up some disk, and this DB averaged 402MB...)

    You should strongly consider shrinking log files that need it, such as ones this overgrown and/or if the # of VLFs is > ~ 200 (command "DBCC LOGINFO" can show you the VLFs). You can gain serious performance sometimes by shrinking the log file and then re-growing it yourself all at once, giving you fewer VLFs and (almost always) more contiguous log files.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.