Can't remove inactive entries from transaction log

My database is set to Full Recovery model. I can shrink my transaction log
by executing a logfile backup, but when I execute a Full or Incremental
backup, this option is disabled. I don't understand why. With the execution
of a successful backup, entries in the log prior to the point of backup
become irrelevant! I might need subsequent logfile transactions in the event
of a failure, but I won't need the logfile prior to the point of backup. So
why can't I shrink the logfile as part and parcel of the backup operation?
Must I always execute a separate logfile backup prior to my Full /
Incremental backup for the sole purpose of shrinking the logfile? I don't
get it.
Thanks for your advice!
- Joe Geretz -
Joseph
If I understood you correctly you have to run LOG file backup with INIT
option to clear all entries prior the FULL backup database.
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>
|||Joe,
A full database backup or differential backup DO NOT truncate the log ( as
you have discovered.) This is by design... Here is a sample backup plan
Full1 Full2
log1 log2 log3 log4 log5,,,, etc
If we die are the log 5 backup, we can restore from FULL2, then restore all
of the logs and we are good to go... But what if the FULL2 backup tape is
missing or corrupted? You can begin by restoring the FULL1 backup, then
log1, log2, log3, log4. etc and still get up-to-the-minute recover without
loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
full or differential database backup was done between 3 and 4. The full,
and differential backups do NOT truncate the log SPECIFICALLY to give us the
opportunity to restore from a grandparent ( FULL1 ) or even older full or
differential backup and not lose data...
If you do not need the log backups, and wish to keep the log small, you
might wish to consider using the Simple recovery model for the database...
This means you will only be able to recover to the last full or differential
backup however.
Hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>
|||Wayne
Why not just clean up with INIT option the LOG file after each FULL backup?
Why to blow the LOG file?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eqD5jF9cEHA.3944@.tk2msftngp13.phx.gbl...
> Joe,
> A full database backup or differential backup DO NOT truncate the log ( as
> you have discovered.) This is by design... Here is a sample backup plan
> Full1 Full2
> log1 log2 log3 log4 log5,,,, etc
> If we die are the log 5 backup, we can restore from FULL2, then restore
all
> of the logs and we are good to go... But what if the FULL2 backup tape is
> missing or corrupted? You can begin by restoring the FULL1 backup, then
> log1, log2, log3, log4. etc and still get up-to-the-minute recover without
> loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
> full or differential database backup was done between 3 and 4. The full,
> and differential backups do NOT truncate the log SPECIFICALLY to give us
the
> opportunity to restore from a grandparent ( FULL1 ) or even older full or
> differential backup and not lose data...
> If you do not need the log backups, and wish to keep the log small, you
> might wish to consider using the Simple recovery model for the database...
> This means you will only be able to recover to the last full or
differential[vbcol=seagreen]
> backup however.
> Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
log[vbcol=seagreen]
> execution
> event
> So
operation?[vbcol=seagreen]
don't
>