Backup - remove inactive entries from transaction log

Last Post 02 Jul 2008 01:28 AM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sqlsc
New Member
New Member

--
26 Jun 2008 04:31 AM
I have 2 questions regarding the option in "backup" to "remove inactive entries from transaction log". This is a checkbox on the options tab when using the SQLServer backup gui.
Question 1.
For most of my databases, this checkbox is NOT greyed out when doing a complete database backup. But on some databases it IS greyed out when doing a complete database backup. All databases are in "full" recovery mode. Why is it greyed out on some databases? (nb all the greyed out ones are on the same SQLServer machine). Note that it is NOT greyed out when doing transaction log backups - but we're talking about complete database backups here.
Question 2.
Consider this scenario.
* complete database backup #1.
* update some data.
* backup the transaction log.
* complete database backup #2, with the option selected to remove inactive entires from the transaction log.
* update some more data.
* Backup the transaction log.
* Now we lose the database, and also backup #2.
What I'm not clear on is in that scenario, are there transaction log records that only exist in the 2nd complete database backup. I.e., if we lost the 2nd complete database backup, is it impossible to roll forward beyond the point of the 2nd complete database backup by rolling forward from the 1st database backup. Do we have a break in the recovery chain?
I would really appreciate a definitive answer on these questions. I was trying to find out for myself by experimenting, but the problem with the option being greyed out on my test machine databases (referred to in question # 1) is preventing me!
SQLUSA
New Member
New Member

--
02 Jul 2008 01:28 AM
Actually, I checked it on one of my servers and it is not greyed out, and it is checkmarked (default setting) for full backup.

Here is a link on it: http://www.microsoft.com/technet/pr...prest.mspx

It states that it is for transaction log backups.

I never used this feature, just left it the way it was checkmarked or uncheckmarked.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
You are not authorized to post a reply.

Acceptable Use Policy