Re: Log File Bloating - Preventative Maintenance

Backing up the files is not the best way. As long as the SQL service is running you won't get a consistent snapshot of the file contents and the backups will be unusable SQL has a transactionally consistent backup method built-in.

Transaction logs are used internally by SQL to guarantee ATOMicity in transaction processing in case of abrupt but recoverable system failure.

Transaction log backups are used to "move" a full backup forward to a given point in time for recoverability.

If once a day recovery is good, then SIMPLE recovery and full backups are fine. If you need point-in-time recovery, then FULL (or BULK-LOGGED) recovery and some regular log backup system is required.

--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP




"Always OpenTo Suggestions" <AlwaysOpenToSuggestions@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:F0580ED4-ABCE-41B6-A05F-4C4B118ECC70@xxxxxxxxxxxxxxxx
Hum, this gives me a new perspective. I'm currently doing a full backup of
.mdf file and .ldf file every night. I see I should back up the .ldf files
throughtout the day.

Doing this, I think I will still have the problem of the ever growing log
file. I think it will still get too large to back up during the day. (it
was 4 times the size of the .mdf yesterday morning.) I MANUALLY change to
SIMPLE, BackedUP, Shrank and Restored database and restit it to FULL to shink
the .ldf to just about nothing. What do I need to do to AUTOMATE keeping my
log files manageable?
--
Thanx in advance - again!

Angelo


"B.Edwards" wrote:

Either:
1. Change the recovery model to SIMPLE and run a nightly full backup and
possibly differential backups throughout the day;
or
2. Leave the recovery model at FULL and run nightly backups and transaction
log backups throughout the day (every 1 or two hours might be a good time).
or
3. Leave the recovery model at FULL; run nightly full backups; run
differential backups periodically during the day;and run transaction log
backups between the differential backups and between the differential
backups and full backup (depending on when you are running the diffs).

in any case if you are running FULL or BULK-LOGGED recovery model you should
be making scheduled log backups regularly.


"Always OpenTo Suggestions"
<AlwaysOpenToSuggestions@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E3D9BDFB-544B-4BD9-BC27-873437DE7B7D@xxxxxxxxxxxxxxxx
> I've got SQL SERVER 2000 and 2005 database which are updated using an
> ACCESS
> application. The .MDF is around 5 gig and the log file recently got up > to
> 25
> gig. To debloat the file, I:
> -- change the Restore Method to SIMPLE
> -- backup the database
> -- shrink the log file
> -- then change Restore Method back to Full.
>
> I do a full backup every night.
> I think I need to keep the Restore Method to Full in case I have a > mid-day
> crash!
> Every month or so I manually shrink the file as described above.
>
> I'm thinking about creating a nightly routine to automate the steps > above.
> My questions are:
> -- Is the above method a sound practice?
> -- Is there any reason I should not automate the above process nightly?
> (or
> weekly)
> -- Am I just missing the boat completely?
>
> Thanx in advance!
>
> Angelo




.

Relevant Pages

  • RE: Transaction Log filling up
    ... You may change database Recovery model to simple if you are not doing ... Transaction log backups are not used, and minimal transaction log space is ...
    (microsoft.public.sqlserver.odbc)
  • Re: Location of Data
    ... > As far as SQL backups, use SQL Enterprise manager to do at least daily ... > backups to your D drive and then let your Backup program bakup the SQL .bak ...
    (microsoft.public.windows.server.sbs)
  • Re: MSDE OSQL Shutdown
    ... dann den Server herunterfährst könnte genau in dem Moment die Aktion ... Aus Gründen der Datenkonsistenz sollten Datenbanken niemals Offline ... weil alle Backups dazwischen unbrauchbar waren;-) ... Datenbank Verzeichnis des SQL Servers dafür vom Dateibackup aus. ...
    (microsoft.public.de.sqlserver)
  • Re: Location of Data
    ... first install), then you will probably reach a point on your second or third ... Moving Data Flolder for Windows Small Business Server 2003 ... >> As far as SQL backups, use SQL Enterprise manager to do at least daily>> backups to your D drive and then let your Backup program bakup the SQL ...
    (microsoft.public.windows.server.sbs)
  • Re: transaction log backups
    ... The best way to handle SQL servers for a quick recovery where you can step ... Then configure one backups of the transaction log to ...
    (microsoft.public.windows.server.sbs)