Re: Log file growing very large??

From: Bill D (Bill_at_test.com)
Date: 08/16/04 

Date: Mon, 16 Aug 2004 12:08:28 -0400

Hello and thanks,

does performing a "backup log <dbname> to disk='d:\backup\dbname.trn' "
clear out and reduce the size of the log as part of its process?

Or do I need to do a "backup log <dbname> with truncate_only" also?

Thanks,
Bill

"Hari Prasad" <hari_prasad_k@hotmail.com> wrote in message
news:OpOydK6gEHA.3992@TK2MSFTNGP11.phx.gbl...
> Hi,
>
>
> Add on to Aaron,
>
> Looks like the recovery model for your database is FULL or
> BULK_LOGGED.Please change the recovery model for the database to
> SIMPLE if your database is non production. Use the below command to set
the
> database to SIMPLE.
>
> ALTER database <DBNAME> set recovery SIMPLE
>
> If you do not have the 65 G to take a backup or if you do not want the
> transaction log backup. You could truncate the transaction
> log and shrink the LDF file.
>
> backup log <dbname> with truncate_only
> go
> DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
>
> If you need the transaction log backup do:-
>
> backup log <dbname> to disk='d:\backup\dbname.trn' ( This might fail
since
> you do not have hard disk space)
> go
> DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
>
> Now execute the below command to see log file size and usage.
>
> DBCC SQLPERF(LOGSPACE)
>
>
> Note:
>
> If you need to keep the recovery model to FULL then schedule a transaction
> log backup in frequent intervals (atleast 30 minutes once).
> This will keep the log file size under control.
>
> Thanks
> Hari
> MCDBA
>
>
>
> "Bill D" <Bill@test.com> wrote in message
> news:eFPpo#5gEHA.3536@TK2MSFTNGP12.phx.gbl...
> > Hello All,
> >
> > All you SQL dba guru's.
> >
> > I have na MSDE server. All seems to be working with app but I looked at
> the
> > server log file and saw that it has grown to 65 gigs. The mdf file is
> only
> > 50 megs.
> >
> > What is causing the log file to grow so large.?
> >
> > Can I just dump the log file?
> >
> > Thanks, Bill
> >
> >
>
>

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: 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)
  • Re: Regarding Transaction Log
    ... The T log file is growing with a greater speed. ... > All modifications are logged in the transaction log. ... > the log is truncated when you backup the log. ... database in simple recovery model, ...
    (microsoft.public.sqlserver.server)
Loading