SQL 2008 Differential backups and the transaction log

Does the transaction log get truncated when performing a differential backup in full recovery mode?
LVL 2
itniflAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.
Ramesh Babu VavillaCommented:
yes
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

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
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.
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?
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.
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?
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.
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.