Full backup and Transaction Logs

Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
doing a Full backup isn't truncating or shrinking the log file. The log
file got to be over 10GB and forced the Data drive to fill up.
Is there some reason for this? I thought when full backups were successful,
they should be truncating the log file.
I can backup log with truncate_only and then issue a dbcc shrinkfile to get
it back down, but I'd like to know why full backups aren't working first.
Thanks.
Kevin A wrote:
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were successful,
> they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to get
> it back down, but I'd like to know why full backups aren't working first.
> Thanks.
>
Nope. See the topic "Truncating the Transaction Log" in Books Online.
If you're running in Full or Bulk-Logged recovery mode, you MUST do
transaction log backups, or the log will continue to accumulate
transactional data. Even in Simple mode, a single large transaction can
cause the log to grow.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Sorry, I just need a bit more clarification.
Are you saying, after a full backup, I need to issue the truncate command?
That doesn't always seem to shrink the Logfile. I have to manually issue
the dbcc srhinkfile.
What would be the suggested steps to take, after the full backup of the DB?
Thanks.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A3927B.3030202@.realsqlguy.com...
> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
> you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Hello,
FULL database backup will not clear the contents in tranasction log file.
The purpose of FULL recovery model is to perform the POIN IN TIME recovery.
In order to do this
you need to take the tranasction log backup. So when ever you perform a
transaction log backup automatically transaction log will be cleared from
the LDF file and will
leep the LDF file growth under control. Incase if you need to do a recovery
on crash/point in time you can use these log backup files. Just read the
Transaction log backup topic in books online.
Thanks
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:eh2Ecu%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were
> successful, they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to
> get it back down, but I'd like to know why full backups aren't working
> first.
> Thanks.
>
|||Hello,
1. First take a look into the recovery model you use for the database.
2. If it is FULL or BULK_LOGGED then you have schedule a transction log
backup in regular intervals [Say every 15 minutes or so]. This frequency can
be changed based on ur data growth,.
Take a look into the BACKUP LOG command to backup the transaction log or you
could use the Maintenence plan wizard to automate the transaction log
backup.
This will make sure that you LDF will not grow drastically.
To reduce the size of 10 GB currently; you may to need to truncate the Log
first and use DBCC SHRINKFILE to reduce the file size. After that to bring
back the backup chain do:-
1. A full database backup
2. Followed with transaction log backups.
THANKS
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:ez%237O%23%23MHHA.4916@.TK2MSFTNGP06.phx.gbl.. .
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually
> issue the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the
> DB?
> Thanks.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45A3927B.3030202@.realsqlguy.com...
>
|||Kevin A wrote:
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually issue
> the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the DB?
> Thanks.
TRUNCATING is not SHRINKING, they are two different operations.
The log file is a "journal", recording every change that takes place in
your database. If the database is in Simple recovery mode, these
journal entries are automatically removed from the log once they've
completed. In Full or Bulk-Logged mode, they remain in the log until
you perform a log backup. This gives you the ability to restore the
database to any point in time, by "playing back" those journal entries
that you've backed up, an ability that you lose with Simple mode.
For example, suppose your database is in Simple mode, and you are doing
full backups nightly at 9:00pm. You suffer a hard drive crash at
8:00pm, corrupting your database. Your only option for recovery is to
restore the backup from 9:00pm the previous night, losing 23 hours of data.
Now, suppose that same database is in Full recovery mode. You're doing
nightly full backups at 9:00pm, and log backups every 5 minutes. Your
drive fails at 8:00pm, corrupting the database. You now have the
ability to restore the previous night's full backup, followed by the log
backups that have been done since then. At most, you lost 5 minutes of
data.
The space inside the transaction log is "recycled", as committed
transactions are flushed out (via a log backup), the space that they
were using is made available for new transactions. This helps to keep
the log file size under control. If you aren't flushing them out (i.e.
TRUNCATING), the log has to grow larger to hold new transactions.
SHRINKING is a different process - it will remove any free space from
the log, physically reducing the size of the log file. You should only
do this under extreme circumstances, it should NOT be part of your daily
routine. Repeated shrinking/growing of a file (log or database) will
lead to fragmentation, which will ultimately hurt performance. If you
have the disk space, you should allow the log to grow as needed, making
sure you're doing proper backups to keep the committed transactions
flushed out.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thank you, you put the very well... Regards, John
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A39711.7070204@.realsqlguy.com...
> Kevin A wrote:
> TRUNCATING is not SHRINKING, they are two different operations.
> The log file is a "journal", recording every change that takes place in
> your database. If the database is in Simple recovery mode, these journal
> entries are automatically removed from the log once they've completed. In
> Full or Bulk-Logged mode, they remain in the log until you perform a log
> backup. This gives you the ability to restore the database to any point
> in time, by "playing back" those journal entries that you've backed up, an
> ability that you lose with Simple mode.
> For example, suppose your database is in Simple mode, and you are doing
> full backups nightly at 9:00pm. You suffer a hard drive crash at 8:00pm,
> corrupting your database. Your only option for recovery is to restore the
> backup from 9:00pm the previous night, losing 23 hours of data.
> Now, suppose that same database is in Full recovery mode. You're doing
> nightly full backups at 9:00pm, and log backups every 5 minutes. Your
> drive fails at 8:00pm, corrupting the database. You now have the ability
> to restore the previous night's full backup, followed by the log backups
> that have been done since then. At most, you lost 5 minutes of data.
> The space inside the transaction log is "recycled", as committed
> transactions are flushed out (via a log backup), the space that they were
> using is made available for new transactions. This helps to keep the log
> file size under control. If you aren't flushing them out (i.e.
> TRUNCATING), the log has to grow larger to hold new transactions.
> SHRINKING is a different process - it will remove any free space from the
> log, physically reducing the size of the log file. You should only do
> this under extreme circumstances, it should NOT be part of your daily
> routine. Repeated shrinking/growing of a file (log or database) will lead
> to fragmentation, which will ultimately hurt performance. If you have the
> disk space, you should allow the log to grow as needed, making sure you're
> doing proper backups to keep the committed transactions flushed out.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||once i have a full backup of the database why would i need the log files
anymore? backupexec 9 indicates they will be removed during a full backup as
i would expect, but it isn't happening on one of my servers. thx if you have
more clues.
"Tracy McKibben" wrote:

> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online.
> If you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||help wrote:
> once i have a full backup of the database why would i need the log files
> anymore? backupexec 9 indicates they will be removed during a full backup as
> i would expect, but it isn't happening on one of my servers. thx if you have
> more clues.
>
Technically, you don't need log files that predate a full backup,
however, it's good insurance to keep as many backups as you can afford
to keep. As the DBA, you're responsible for protecting the company's
data, so keep as many backups as you can.
As for why they're not being deleted by BackupExec, I have no idea. I
don't use third party backup tools like that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com