If you want to be able to recover the database, you must BACKUP DATABASE
immediately after the BACKUP LOG WITH NO_LOG.

NO_LOG should only really be used when the log is so full that BACKUP LOG
WITH TRUNCATE_ONLY fails due to lack of logspace.

If there is no need to be able to recover the database, turn on trunc. log
on chkpt. database option (RECOVERY SIMPLE on SQL 2K) - after truncating as
above.
Once the log is truncated you will be able to SHRINK it via EM or DBCC
SHRINKFILE

> -----Original Message-----
> From: Farnsworth, Dave [SMTP:[Email Address Removed] Sent: Tuesday, November 19, 2002 3:55 PM
> To: LazyDBA.com Discussion
> Subject: RE: Transaction log space
>
> Have you backed up the log?? If not you can do this in QA
>
> BACKUP LOG name_of_database WITH NO_LOG
>
> This will remove all the committed transaction from the log and give this
> space to the free portion of the log. Then if you want to get back some
> disk space from the T-log, you can do this
>
> 1) in QA run this in the database in question
> EXEC SP_HELPFILE
>
> This will give the name of the data file and log file
>
> 2) then in QA do this
> DBCC SHRINKFILE(log_file_name, target_size_in_MB)
>
> If the backup log does not free up space you may have a problem with the
> position of the active virtual log in the log file. If this is the case
> let me know and we can fix that too.
>
> Dave
>
> -----Original Message-----
> From: Phil Shuttleworth [mailto:Phil.[Email Address Removed] Sent: Tuesday, November 19, 2002 9:41 AM
> To: LazyDBA.com Discussion
> Subject: Transaction log space
>
>
> Hallo DBA's,
>
> I have a problem with one of our SQL Server 7.0 databases, in that the
> Transaction log seems to be growing out of control.
>
> The figures are as follows:-
>
> Data space Used Free Total
>
> Database 416.13 MB 26.88Mb 443.00 MB
>
> Transaction log space 2,652.24 MB 126.50 MB 2,778.74 MB
>
> As you can see, the Transactiion log space (used) is about 6 times the
> size
> of the Data space (used), and the transaction log free space is also out
> of
> proportion to a similar amount. This has been growing steadily over the
> past few weeks.
>
> Does anyone know how to reduce this to a reasonable size, please?
>
>
> Phil Shuttleworth
>
> DBA, IT Services
> University of Central England, BIRMINGHAM B42 2SU, UK
> Tel: +44(0)121 331 5664 Fax: +44(0)121 356 2875
> E-mail: Phil.[Email Address Removed]
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional commands, e-mail: mssqldba-[Email Address Removed]
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page