Re: questions about logging

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Dan (ddonahue_at_archermalmo.com)
Date: 05/17/04 

Date: Mon, 17 May 2004 09:14:29 -0500

Thanks Dan
"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:%233wbyhpOEHA.3988@tk2msftngp13.phx.gbl...
> You should choose a database recovery model based on your recovery
> requirements. If your recovery plan is to simply restore from your last
> full backup, the SIMPLE recovery model is appropriate.
>
> Your transaction log still needs to be sized to accommodate the largest
> single transaction. You can minimize log space requirements and improve
> performance by taking advantage of minimally logged operations
> (SELECT...INTO, 'fast' bulk insert, TRUNCATE) when possible. These
> techniques are commonly used in an ETL process.
>
> A database log file should never be deleted. SQL Server is forgiving in
> many cases but you run the risk of trashing your database when you
manually
> delete a log file. If you need to reduce the size of a log file, use DBCC
> SHRINKFILE.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Dan" <ddonahue@archermalmo.com> wrote in message
> news:OglhqveOEHA.2716@tk2msftngp13.phx.gbl...
> > We're using SS2000. We do a lot of ETL (extract, transform, load) work
and
> > don't really need logs. From what I've read it seems that either a
> > bulk-logged or simple recovery plan would be better than full recovery.
> > Could someone recommend one over the other?
> >
> > We use a lot of DTS packages and we were thinking of truncating the logs
> > before starting a package. I've also seen scripts that loop through the
> > database and truncate logs. I've seen a "backup log dbname with
> > truncate_only" command and I've also seen a script that just deletes the
> log
> > file. Is one way better than another or is there a better way?
> >
> > Thanks,
> >
> > Dan
> >
> >
>
>

Relevant Pages

  • Re: Simple recovery model + checkpoint = no need to save .ldf file?
    ... The files can also be moved to another server and ... writes all changes to the data files before the database is detached. ... you do not need the log file as sp_attach_single_file_db can be used ... > If I have a sql server 2000 database using the simple recovery model and ...
    (microsoft.public.sqlserver.server)
  • Re: Will this shrinkfile method work?
    ... There is no need to change the recovery model at all. ... If the active VLF is near the end of the log file it may take a little ... while for it to shrink but it will. ... Right click the database, choose TASKS - SHRINK - DATABASE ...
    (microsoft.public.sqlserver.setup)
  • Re: Log file growing very large??
    ... > BULK_LOGGED.Please change the recovery model for the database to ... You could truncate the transaction ... > Now execute the below command to see log file size and usage. ...
    (microsoft.public.sqlserver.msde)
  • Re: WSUS 2.0 grew to 16 GB - need to fix
    ... It seems that the log file is growing (.ldf file) and that means that something could have changed the recovery model of the database. ...
    (microsoft.public.windows.server.sbs)
  • Re: Please evaluate this approach to shrinking log files
    ... A common cause of unruly log files is that the database is in the FULL ... recovery model but regular transaction log backups are not scheduled. ... IMHO, log file ...
    (comp.databases.ms-sqlserver)