Primary Database Backup in Log Shipping Scenario

I've setup a Log Shipping scenario for one of our databases. This works fine.
Also, every night a full database backup of the primary database is made to a
disk location. Normally, the transaction log would be truncated after a full
database backup. This behavior is unwanted in a Log Shipping scenario since
the content of the transaction log needs to be applied to the secondary
database. We've seen that since the Log Shipping scenario is in place, the
log is no longer truncated after the full database backup of the primary
database. How does the primary database "know" not to truncate the
transaction log? In other words, I'd like to know how the full database
backup mechanism works in a Log Shipping environment.
Are you saying you with issue a backup log with truncate_only statement
after your backup has finished?
ie
backup log fulltext with truncate_only
If so, this will break your log shipping chain and should not be done.
The way a log shipping works is a backup is done. Embedded in the backup is
the LSN (Log Sequence Number) for the transaction log. The next log backup
you do contains log enteries starting at the lsn for this last backup. So
you can restore the log to the database backup where the lsn matches the lsn
embedded in the backup.
If you then deploy log shipping and do a backup the backup entry is in the
log but it is ignored, so you can continue to apply subsequent log dumps
without breaking the chain.
Does this answer your question?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wilbert" <Wilbert@.discussions.microsoft.com> wrote in message
news:542507D8-50F4-4CEC-BEE5-36DF1EBBA607@.microsoft.com...
> I've setup a Log Shipping scenario for one of our databases. This works
> fine.
> Also, every night a full database backup of the primary database is made
> to a
> disk location. Normally, the transaction log would be truncated after a
> full
> database backup. This behavior is unwanted in a Log Shipping scenario
> since
> the content of the transaction log needs to be applied to the secondary
> database. We've seen that since the Log Shipping scenario is in place, the
> log is no longer truncated after the full database backup of the primary
> database. How does the primary database "know" not to truncate the
> transaction log? In other words, I'd like to know how the full database
> backup mechanism works in a Log Shipping environment.
|||Hello Hilary,
I'll clarify my question: I'd like to know how SQL Server "knows" whether a
Log Shipping scenario is in place, and I'd like an explanation to the
subsequent difference in behavior when performing a Full Database backup
(truncate the log in "normal" operation and not truncating the log in a Log
Shipping scenario).
Best regards, Wilbert
|||SQL Server does not know if a log is being shipped or not. Log shipping fits
into the existing point in time recovery scheme that sql server uses to
recover databases.
If you truncate a log it will remove all committed entries in that log.
Logged open transaction and other non-committed commands will remain in the
log since the last checkpoint. There is no log truncation after a back up in
full recovery model, not in bulk logged. The problem with bulk logged is
that minimally logged operations are minimally logged and will destroy your
log shipping chain.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wilbert" <Wilbert@.discussions.microsoft.com> wrote in message
news:00B5F549-71CE-4089-A81A-A537B6BA212F@.microsoft.com...
> Hello Hilary,
> I'll clarify my question: I'd like to know how SQL Server "knows" whether
> a
> Log Shipping scenario is in place, and I'd like an explanation to the
> subsequent difference in behavior when performing a Full Database backup
> (truncate the log in "normal" operation and not truncating the log in a
> Log
> Shipping scenario).
> Best regards, Wilbert
|||Hello Hilary,
Isn't it so that after a full database backup the committed entries in the
log are removed?
That's the origin of my question: in "normal" operation these entries are
removed from the log, while in a log shipping scenario this doesn't seem to
be the case. This led me to believe that SQL Server somehow "know" that log
shipping is taking place and that the entries in the log should be retained
until the next log backup.
Best regards, Wilbert
|||No, the log is insert and read only. What happens is that it is a record of
all of your database activity. Before anything hits disk it is written to
your log. When you issue a checkpoint the checkpoint is written to the log
and all dirty pages (including uncommitted pages are written to disk).
Should a failure occur the log is consulted to find the last checkpoint and
uncommitted transactions which occurred after the last checkpoint are
removed from disk and committed transactions which committed after the last
checkpoint are written to disk.
When you do a db backup nothing happens to the log other than the backup
happened. When you do a log backup, the internal vlf's in the log buffer I
believe have their status changed to 0 if there are no active transactions
in them. But the log itself does not have anything happen to it.
The above applies to full recovery model.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wilbert" <Wilbert@.discussions.microsoft.com> wrote in message
news:668C79A5-01E2-4336-94D9-EB76B8D23866@.microsoft.com...
> Hello Hilary,
> Isn't it so that after a full database backup the committed entries in the
> log are removed?
> That's the origin of my question: in "normal" operation these entries are
> removed from the log, while in a log shipping scenario this doesn't seem
> to
> be the case. This led me to believe that SQL Server somehow "know" that
> log
> shipping is taking place and that the entries in the log should be
> retained
> until the next log backup.
> Best regards, Wilbert