Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

SQL 2008 Differential backups and the transaction log

Does the transaction log get truncated when performing a differential backup in full recovery mode?
0
itnifl
Asked:
itnifl
  • 3
  • 2
  • 2
  • +3
3 Solutions
 
TempDBACommented:
Generally. Here is more on it
Under the full recovery model or bulk-logged recovery model, the inactive part of the log cannot be truncated until all its log records have been captured in a log backup. This is needed to maintain the log chain—a series of log records having an unbroken sequence of log sequence numbers (LSNs). The log is truncated when you back up the transaction log, assuming the following conditions exist:

    A checkpoint has occurred since the log was last backed up. A checkpoint is essential but not sufficient for truncating the log under the full recovery model or bulk-logged recovery model. After a checkpoint, the log remains intact at least until the next transaction log backup.

    For more information, see Checkpoints and the Active Portion of the Log.

    No other factor is preventing log transaction.

    Generally, with regular backups, log space is regularly freed for future use. However, various factors, such as a long-running transaction, can temporarily prevent log truncation. For more information, see Factors That Can Delay Log Truncation.

    The BACKUP LOG statement does not specify WITH COPY_ONLY.


Reference:-http://technet.microsoft.com/en-us/library/ms189085.aspx
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to answer the question:
no. a differential backup does NOT issue a log truncation.

only full backup and transaction log backups do truncate the log
=> note: this will not shrink the file physically, only mark the space used as reusable.
0
 
Ramesh Babu VavillaCommented:
yes
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
VENKAT KOKULLASQL Server DBACommented:
http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-the-log-and-log-backups-how-to-convince-yourself.aspx

The above link will make your things clear pls go through the link

--Venkat
0
 
Anthony PerkinsCommented:
Notice the first sentence from Paul's blog:
There's still a widely held misconception that when properly in the FULL or BULK_LOGGED recovery models that full or differential backups can truncate the log. No. It *NEVER* happens

That leaves only one type of backup that will truncate the log.
0
 
itniflAuthor Commented:
So when I have full, differential and transaction logs backups, I could choose to ignore to restore the differential backup and only restore transaction log backups since the last full backup? But that would be a bigger administrative burden. I guess I would prefer to restore the full, then the differential backups and then in the end the transaction log backups that occured after the last differential?
0
 
Anthony PerkinsCommented:
>>I guess I would prefer to restore the full, then the differential backups and then in the end the transaction log backups that occured after the last differential?<<
Consider requesting that enhancement from the MS SQL Server development team.  It is too late for "Denali", but it may be in time for the following version to be released circa 2018.
0
 
itniflAuthor Commented:
acperkins: So you are saying that I would only be able to restore the full backup and the all the transaction log backups? When would I want to restore the differential backups then?
0
 
Anthony PerkinsCommented:
>>So you are saying that I would only be able to restore the full backup and the all the transaction log backups?<<
I don't know I have never done it.

>>When would I want to restore the differential backups then? <<
Perhaps when you want to restore to when the differential backup was run.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can restore a full back

then
* restore 1 differential backup, optionally
OR
* restore 1 or more incremental backups, optionally

then
restore 1 or more transaction logs, optionally

only for the last restore item, you specify WITH RECOVERY, all the others before are done WITH NORECOVERY, otherwise you cannot continue to restore.

to be able to do transaction log backups, you must have your db in full recovery or bulk-logged mode
to be able to restore those log backups, there must though be no bulk-logged action since the last full or differential backup.

this means, as consequence:
* if you are running in simple recovery mode, the differential or incremental backups are the only way to come "close" to the point in time you want to restore to
* if you have a bulk-logged operation, you need to run either a full or a differential backup, before you can run a usable transaction log backup

ONLY the full backup and the transaction log backups will mark the space used in the transaction log by completed (!) transactions as reusable.
=> the file on disk will NOT shrink because of this, only a DBCC SHRINKFILE can do that (but that is another discussion)
=> active transactions that are not completed (aka COMMIT or ROLLBACK missing) will keep the transaction log growing, because the space cannot be "reused" (use DBCC OPENTRAN command to "see" if you have such a "old" open transaction to eventually kill it)

hope this clarifies

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now