Posted by Erland Sommarskog on 07/02/05 19:14

[posted and mailed, please reply in news]

Peter CCH (petercch.wodoy@gmail.com) writes:
> Database log of my DB is around 2GB.
> The database is using FULL recovery option.
>
> I want to reduce the file size of the log cause it takes up a lot of
> space.
>
> I'd do a full database backup, then backup the transaction log as well
> ... both backup performed with a check on the option "clear inactive
> entries from transaction log".
>
> But after I backup, the database log is still 2GB.
>
> What should I do to reduce the database log file size?
> Should I use?:
>==============================
> Dump Tran databaseName with no_log
> DBCC shrinkdatabase(databaseName, 30)
>==============================
>
> Is that safe to be used in production server?

First of alll, think twice before you start to shrink files at all.
If you know that your database have experienced a heavy once-in-a-blue-
moon update, then it could make sense to shrink the log. But if the
log is 2GB because of daily operations, the log will grow again. And
while the log is growing you lose performance on you server. And the
log file may be fragmented on file-system level.)

If you use NO_LOG, you must take a full backup, or else you will no
long have any up-to-the-point recovery option from that point and on.

DBCC SHRINKFILE is better to shrink the log file than DBCC SHRINKDATABASE
that will also operate on the data file.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

[Back to original message]