Backup - remove inactive entries from transaction log

Last Post 02 Jul 2008 12:28 AM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

26 Jun 2008 03: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!
New Member
New Member

02 Jul 2008 12: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:

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:

Acceptable Use Policy