Re: Clear/Purge Log files

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 05/19/04 

Date: Wed, 19 May 2004 08:02:02 +0530

Hi,

Instead of detaching , delete the LDF file, Attach the database ytou should
have tried the below steps:-

Alter database <dbname> set single_user with rollback immediate
go
backup log <dbname> to disk='d:\backup\dbname.trn1'
go
dbcc shrinkfile('logical_log_name',truncateonly)
go
Alter database <dbname> set multi_user

After executing the above you can execute the below command check the log
file size and usage,

dbcc sqlperf(logspace)

Like Dan suggested go for SIMPLE recovery model if your data is not critical
or you not require a transaction log based recovery (POINT_IN_TIME).

Thanks
Hari
MCDBA

"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:uTGdG0TPEHA.2256@TK2MSFTNGP10.phx.gbl...
> The way you manage your log file is driven by your database recovery plan.
> If your recovery plan is to restore from your last full backup and not
apply
> transaction log backups, then change your database recovery model to
SIMPLE.
> This will keep your log size reasonable by removing committed transactions
> from the log. The log will still need to be large enough to accommodate
> your largest single transaction. If you want to reduce potential data
loss,
> you should use the BULK_LOGGED or FULL recovery model and backup your log
> periodically.
>
> The proper way to shrink files is with DBCC SHRINKFILE. See the Books
> Online for details. You should not need to do this as part of routine
> maintenance.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Carlos" <wt_know@hotmail.com> wrote in message
> news:OueiitTPEHA.620@TK2MSFTNGP10.phx.gbl...
> > The MDF file is 800 MB but the LDF file is 5 GB. I run the "shrink
> database"
> > but this does not seems to reduce the mdf or ldf filesize.
> >
> > What I did is detach the database, delete the ldf file, re-attach the
> > database to create a new ldf file. If I do not do so, the application
> cannot
> > work (hang!) because the ldf file is too huge and it takes ages to
commit
> a
> > transaction. Is there a "better" way to control the ldf file like
> > auto-purging ? Should I restrict the log file size to a specific
filesize
> > like 500MB ? Does this mean it will auto-purge each time it reach 500MB
> for
> > the ldf file ?
> >
> > Thanks
> >
> >
> >
>
>

Relevant Pages

  • 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)
  • What is the best practice to create Maintenance Plans
    ... There is not a definitive answer to the question of which backup is better. ... the database can be recovered to the point ... Full Recovery or Bulk-Logged Recovery model. ... Transaction Log backup for master database is not allowed. ...
    (microsoft.public.sqlserver.security)
  • RE: What is the best practice to create Maintenance Plans
    ... > If master is damaged in some way, for example because of media failure, an ... > With the Simple Recovery model, the database can be recovered to the point ... > Full Recovery or Bulk-Logged Recovery model. ... > Transaction Log backup for master database is not allowed. ...
    (microsoft.public.sqlserver.security)
  • Re: Sharepoint and SQL Recovery Model
    ... SQL Server 2000 uses recovery models to help you plan backups. ... You can use either Simply or Full Recovery Model based on your need. ... be recovered to the point of the last backup. ... The SharePoint Portal Server 2003 configuration database. ...
    (microsoft.public.sharepoint.portalserver)
  • Re: transaction log
    ... It seems your Recovery model set to that database is "FULL". ... So we need to scdule a transaction log backup regularly (atleast hourly ...
    (microsoft.public.sqlserver.server)