Post reply

Database Maintenance Plans

  • November 7, 2002 at 10:02 am

    #58680

    I have setup a Database Maintenance Plan, in which I am taking a transactional log backup.

    my question is if we are taking a transactional log backup then it should truncate the transaction log for that database.

    thanks in advance

  • November 7, 2002 at 10:52 am

    #440684

    From "Transaction Log Backups" in SQL 2000 BOL

    quote:

    Truncating the Transaction Log

    When SQL Server finishes backing up the transaction log, it automatically truncates the inactive portion of the transaction log. This inactive portion contains completed transactions and so is no longer used during the recovery process. Conversely, the active portion of the transaction log contains transactions that are still running and have not yet completed. SQL Server reuses this truncated, inactive space in the transaction log instead of allowing the transaction log to continue to grow and use more space.

    Although the transaction log may be truncated manually, it is strongly recommended that you do not do this, as it breaks the log backup chain. Until a full database backup is created, the database is not protected from media failure. Use manual log truncation only in very special circumstances, and create a full database backup as soon as practical.

    The ending point of the inactive portion of the transaction log, and hence the truncation point, is the earliest of the following events:

    The most recent checkpoint.

    The start of the oldest active transaction, which is a transaction that has not yet been committed or rolled back.

    This represents the earliest point to which SQL Server would have to roll back transactions during recovery.

    The start of the oldest transaction that involves objects published for replication whose changes have not been replicated yet.

    This represents the earliest point that SQL Server still has to replicate.

    Note: Truncating a log does not free the empty space to the OS so the file size will remain, the transactions are just removed. Also note a Full backup does not truncate the log, see BOL for more details.

  • November 7, 2002 at 11:09 am

    #440691

    yes

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • November 7, 2002 at 11:52 am

    #440702

    Thanks guys. It's truncating.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply