full backup and transaction log truncate

Hi,
I'm about how the backup system works in SQL Server. I'm using
the BULK LOGGED recovery model, I do regular full backups but my log
file is growing very large.
BOL says:
Full database backup, which backs up the entire database including the
transaction log
and it also says the BACKUP LOG truncates the log file, so I assumed
that BACKUP DATABASE would truncate the log file as well, but it doesn't
seem to be doing that. So the question is:
Does doing a full backup using BACKUP DATABASE truncate the log file?
Although I might be by the statement in BOL I actually hope
that BACKUP DATABASE does not truncate the log file, that way I can do
full backups whenever I want, for whatever reason and these backups will
not interfere with an automated full/differential/trans log backup system.
Thanks.BACKUP DATABASE does not truncate the inactive log portion. This means that
full backups do not interrupt a log backup sequence. If your most recent
backup is bad or incomplete, you can use an older backup as a starting point
and restore more logs to get back to the desired recovery point in time.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"John" <no@.spam> wrote in message
news:edF0K$deGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm about how the backup system works in SQL Server. I'm using
> the BULK LOGGED recovery model, I do regular full backups but my log file
> is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed that
> BACKUP DATABASE would truncate the log file as well, but it doesn't seem
> to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be by the statement in BOL I actually hope that
> BACKUP DATABASE does not truncate the log file, that way I can do full
> backups whenever I want, for whatever reason and these backups will not
> interfere with an automated full/differential/trans log backup system.
> Thanks.|||John,
You have to backup the log in order to allow sql server to re-use the VLF
before the MinLSN. If these VLFs can not be reused and we reach the end of
the transaction log, then sql server will have to expand it.
Truncating the Transaction Log
http://msdn.microsoft.com/library/d...r />
_7vaf.asp
How to stop the transaction log of a SQL Server database from growing
unexpectedly
http://support.microsoft.com/?kbid=873235
AMB
"John" wrote:

> Hi,
> I'm about how the backup system works in SQL Server. I'm using
> the BULK LOGGED recovery model, I do regular full backups but my log
> file is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed
> that BACKUP DATABASE would truncate the log file as well, but it doesn't
> seem to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be by the statement in BOL I actually hope
> that BACKUP DATABASE does not truncate the log file, that way I can do
> full backups whenever I want, for whatever reason and these backups will
> not interfere with an automated full/differential/trans log backup system.
> Thanks.
>