Re: Log file size not reduce

pb648174 (google@xxxxxxxxxxx) writes:
> I use the below statement. The concept of "it is just going to grow
> again" is bogus. If you don't shrink it, it will eventually take up the
> entire hard drive it is on (at least in our experience) and we have
> experienced no performance problems with shrinking it every day to one
> megabyte.

As David said, if you need to shrink it daily, you have a problem that
you should fix. If the log keeps growing, the most likely reason can be
because you are running with full or bulk-logged recovery, but are not
taking regular log backups. (Backing up the database does not truncate the
log.)

> use DBTest
> go
>
> DBCC SHRINKFILE(DBTest_log, 1)
> BACKUP LOG DBTest WITH TRUNCATE_ONLY
> DBCC SHRINKFILE(DBTest_log, 1)

Well, I know a better way: if you don't care about up-to-the-point recovery,
and are content with restoring the latest backup in case of a crash, set
the recovery mode to simple, and SQL Server will regularly truncate the
transaction log.

If your business requirements do call for up-to-the-point recovery,
you should never use WITH TRUNCATE_ONLY unless there is real emergency.
And in such case, you should take a full backup directly.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
.

Relevant Pages

  • Re: i am afraid backup log with no_truncate is useless .
    ... Also, if the whole SQL Server goes down, then you can still get up to minute recovery. ... Backup log with no truncate behaves correctly at sql server 2000, in as much as you can back up the lock even ... when the mdf is unavailable. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 2005 Shrink issue
    ... you can't take a transaction log backup when your database in SIMPLE Recovery Model. ... shrink on the .ldf DB's in full mode? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.tools)
  • Re: Logged Actions
    ... invalidates the log or not is whether sufficient information is written to ... and nowhere else (e.g., in SIMPLE recovery model), there's no way SQL Server ... I don't see why SQL Server cannot roll this operation ... > table command it invalidated the tran log backup and you would have to do ...
    (microsoft.public.sqlserver.programming)
  • Re: HDD still full after shrinking transaction log
    ... i did a backup for the DB and log a few times ... In the first case, you should run with full recovery, and backup your ... SQL Server will then truncate the transaction log regularly. ...
    (comp.databases.ms-sqlserver)
  • RE: Dbase Backup File Size
    ... Index rebuild could cause the shrink, because the data would have been ... In a SQL Server backup each page is 8KB, ... If you never ran index optimization on your database the database gets large ...
    (microsoft.public.sqlserver.server)