Reduce the size of log file (SQL Server 2000)

Dear all,
I want to reduce the size of log file, what should I do ?
Thanks.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=873235 How to stop the log file from
growing
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
http://www.support.microsoft.com/?id=307487 Shrinking TempDB
Andrew J. Kelly SQL MVP
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:%23BGLTKrOHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> I want to reduce the size of log file, what should I do ?
> Thanks.
>
|||Vensia wrote:
> Dear all,
> I want to reduce the size of log file, what should I do ?
> Thanks.
>
First, determine why it's grown to the current size. Is it because
you're not doing transaction log backups, or does your normal day-to-day
activity require it to be that size?
Quite often, when someone asks this question, it's because they have the
database in Bulk-Logged or Full recovery mode, without understanding
what that means. The transaction log is a sort of "journal", recording
all of the changes that occur in your database. As these journal
entries accumulate, the log file must grow. You can minimize this
growth in 2 ways:
- put the database in Simple mode. Committed transactions are
automatically flushed from the log. You lose the ability to recover the
database to the point of failure, however.
- schedule periodic transaction log backups. A log backup flushes out
the committed transactions, AND gives you the ability to restore a
database to literally any point in time, by restoring the last full
backup, followed by all of the transaction log backups up to the point
of failure. Doing log backups every 15 minutes means you should never
lose more than 15 minutes of work if the database should crash.
It is important to understand that flushing out these transactions
(known as TRUNCATING the log) will NOT reduce the physical size of the
log file. Truncating simply removes the committed transactions, making
that space available for re-use by future transactions.
To reduce the physical size of the log file, you must perform a SHRINK,
using DBCC SHRINKFILE. Think carefully before doing this. Think about
WHY the log file is the size that it is - what made it this size?
Shrinking it makes no sense if it's just going to grow again, you'll end
up with file fragmentation, affecting performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks for Tracy and Andrew.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AF7638.9080009@.realsqlguy.com...
> Vensia wrote:
> First, determine why it's grown to the current size. Is it because
> you're not doing transaction log backups, or does your normal day-to-day
> activity require it to be that size?
> Quite often, when someone asks this question, it's because they have the
> database in Bulk-Logged or Full recovery mode, without understanding
> what that means. The transaction log is a sort of "journal", recording
> all of the changes that occur in your database. As these journal
> entries accumulate, the log file must grow. You can minimize this
> growth in 2 ways:
> - put the database in Simple mode. Committed transactions are
> automatically flushed from the log. You lose the ability to recover the
> database to the point of failure, however.
> - schedule periodic transaction log backups. A log backup flushes out
> the committed transactions, AND gives you the ability to restore a
> database to literally any point in time, by restoring the last full
> backup, followed by all of the transaction log backups up to the point
> of failure. Doing log backups every 15 minutes means you should never
> lose more than 15 minutes of work if the database should crash.
> It is important to understand that flushing out these transactions
> (known as TRUNCATING the log) will NOT reduce the physical size of the
> log file. Truncating simply removes the committed transactions, making
> that space available for re-use by future transactions.
> To reduce the physical size of the log file, you must perform a SHRINK,
> using DBCC SHRINKFILE. Think carefully before doing this. Think about
> WHY the log file is the size that it is - what made it this size?
> Shrinking it makes no sense if it's just going to grow again, you'll end
> up with file fragmentation, affecting performance.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com