SQL "Remove inactive entries from transaction log"

In SQL Server Backup, there is a check box that says:
"Remove inactive entries from transaction log"

can someone explain the meaning of this?


Who is Participating?
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.

Anthony PerkinsCommented:
It is all covered in BOL under the section: "Truncating the Transaction Log"
Aneesh RetnakaranDatabase AdministratorCommented:
This ensures that inactive entries are cleared out of the transaction log after a backup.
Remove inactive entries from transaction log causes the inactive portion of the transaction log to be truncated, allowing SQL Server to reuse this truncated, unused space.

Each server has a transaction log which stores information about committed transactions coordinated by the server that may not have been completed.Deleting transaction log files may cause inconsistencies in your data. You should set the TransactionLogFilePrefix so that transaction log files are created on a highly available file system, for example, on a RAID device. To take advantage of the migration capability of the Transaction Recovery Service for servers in a cluster, you must store the transaction log in a location that is available to a server and its backup servers, preferably on a dual-ported SCSI disk or on a Storage Area Network (SAN). The transaction log buffer is limited to 250 KB.

Every SQL Server database has at least two files associated with it: one data file that houses the actual data and one transaction log file. The transaction log is a fundamental component of a database management system. All changes to application data in the database are recorded serially in the transaction log. Using this information, the DBMS can track which transaction made which changes to SQL Server data.

When Microsoft SQL Server finishes backing up the transaction log, it truncates the inactive portion of the transaction log. This frees up space on the transaction log. SQL Server can reuse this truncated space instead of causing the transaction log to continuously grow and consume more space. The active portion of the transaction log contains transactions that are still running and have not completed yet.
Microsoft SQL Server will try to take a checkpoint whenever the transaction log becomes 70 percent full, if a log full error occurs, and when SQL Server is shut down (unless using the NOWAIT option) it will take a checkpoint for each database.
The transaction log should not be backed up if the truncate log on checkpoint database option is set to TRUE. If you specify truncate log on checkpoint to be true, Microsoft SQL Server will clear out inactive transaction log entries at every checkpoint. This option essentially tells SQL Server that you will not be using the transaction log for restore operations. The transaction log is still required to roll back transactions and for SQL Server to determine how to recover databases when it is restarted. Use this option only for systems where it is okay for you to lose transactions during the course of a day, because you will only be able to restore your database back to the last backup that was taken. Applications of this nature are very rare in most production environments.
If a transaction log has been truncated (except by a BACKUP LOG) you should not backup that log until you take a database backup or differential database backup. A differential database backup will backup only data that has changed since the last full database backup.

You should also avoid backing up transaction logs any nonlogged operations have occurred in since the last database backup was created. Create a database or differential database backup instead.
And finally, if any files are added or deleted from the database, a transaction log backup should not be taken. Instead, you should create a full database backup.

Chetan Sachdeva
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jskfanAuthor Commented:
I will get back with you later guys
Anthony PerkinsCommented:

If you cannot put it in your own words and all you are going to do is copy and paste text from the Internet, please give credit to the author, as in:

WebLogic Server 7.0
Managing Transactions

Transaction Log Guidelines by Craig S. Mullins

This is all covered here in the EE Guidelines:
 ...Your Content can be viewed...

If you don't want people to see your content, don't post it. If it's not yours, then give credit; you'll frequently see links to content on other sites, which is the preferred method of quoting some third party source. This is particularly true of copyrighted information from websites like the Microsoft Developer's Network. See the paragraph on intellectual property for further clarification.


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
jskfanAuthor Commented:
I have read that on SQL Books online, and didn't understand incative entries.

let's break it down through this illustration.

-  SQL writes data to logs before those logs get commited to the database
-  logs are files, I guess 8kb each  correct
-  when a log file is commited to the database, means the data is transferred to the database, but the log file stays the same( of course if it's not in simple mode).
- when we do the full back the commited log files get deleted.

Now the inactive entries are they located inside each log file? if so,  how do they get created?


jskfanAuthor Commented:
I guess Microsoft uses  the expression " inactive entries" to refer to the logs that have been commited and haven't been backed up yet in order to be deleted.

Anthony PerkinsCommented:
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

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.