Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1898
  • Last Modified:

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?


4 Solutions
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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.

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:

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now