SQL "Remove inactive entries from transaction log"

Posted on 2006-04-17
Last Modified: 2009-07-29
In SQL Server Backup, there is a check box that says:
"Remove inactive entries from transaction log"

can someone explain the meaning of this?


Question by:jskfan
    LVL 75

    Expert Comment

    by:Anthony Perkins
    It is all covered in BOL under the section: "Truncating the Transaction Log"
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    This ensures that inactive entries are cleared out of the transaction log after a backup.
    LVL 4

    Assisted Solution

    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

    Author Comment

    I will get back with you later guys
    LVL 75

    Accepted Solution


    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.


    Author Comment

    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?



    Author Comment

    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.

    LVL 75

    Assisted Solution

    by:Anthony Perkins

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Introduction If you are not already aware of what you could use a table with sequential integer values for in SQL, you can read Delimited String Parsing in SQL Server 2005 or later ( by BrandonGalderisi (h…
    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now