SQL Server Performance

AutoTruncate Mode

Discussion in 'SQL Server 2008 General DBA Questions' started by Hrishi_nk, Aug 23, 2011.

  1. Hrishi_nk New Member

    I have been reading about Autotruncate mode in SQL server.
    I found that SQL server truncates the log in 2 conditions.
    1)When recovery model is Simple
    2)When you have never taken a full database backup.
    I read it in a book "SQL Server 2008 internals by microsoft press".
    So the condition 2 which i have mentioned does that apply to a database whose
    recovery model is full or bulk log suppose if i have not taken a backup of that particular database.
    As far as i have read SQL server truncates the log (Full Recovery)
    only when we backup the transaction log.
    So does the Autotruncate Mode exists in Full Recovery model if DB has not been backed up
  2. satya Moderator

    Welcome to the forums.
    True and as per BOL: You can discover what, if anything, is preventing log truncation by using the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view.
    With the recovery FULL or BULK_LOGGED the log records needs to be truncated witha BACKUP LOG statement, that is the reason you will see the log growth when there is no Tlog backup until the disk space is vanished on the drive where Tlog files are located.
  3. preethi Member

    Welcome to forums.
    To make it simple,
    1. Under full recovery model, the transaction log could be marked for truncation only after a backup of transaction log. (But this is not the only condition.)
    2. Full recovery model comes to force only when a full backup is taken. Until a backup is taken, recovery model is meaningless. (Why talk about recovery when there is no backup anyway)

    Hope this helps.
  4. satya Moderator

    ... not entirely :), in case of server shutdown/restart it will take long time to RECOVER provided no corruption in the file ...

    In any case for POINT IN TIME recovery you must deploy meaningful backup strategy where..using FULL recovery is a possibiliy.

Share This Page