Post reply

Does full backup will truncate log?

  • December 21, 2010 at 5:36 am

    #388476

    Sorry for My English, ^-^

    I have a question these days for Full backup.

    I created a database by T-sql as following

    Create database DB

    dbcc log(db)

    Then it have about 67 rows

    but after I run a full backup T-sql

    backup database DB to disk='c:\DB'

    go

    dbcc log(db)

    I found it only have left 10 rows.

    As I known,full backup doesn't truncate the log ,but why my rows number reduced

    Thanks

  • December 21, 2010 at 5:45 am

    #1265009

    If the database is in simple recovery, log records are truncated on checkpoint (which a backup runs).

    If the database is in full recovery, log records are truncated on checkpoint (which a backup runs) until the first full backup has completed.

    Check your recovery model, set to full if it's not, then test once a full backup has run, otherwise the DB is behaving as though it's in simple recovery.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 21, 2010 at 6:18 am

    #1265024

    Thans for your response, Gail Shaw

    I checked the recovery model and It's in full recovery.

    And after a full-backup,I created some table,then I run the "dbcc log(db)", It returned about 600 rows

    Then I run a full-backup,and dbcc log again .It returned about 800 rows

    As you said,my prolem was caused by "checkpoint",not full-backup

    Thans for you help

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

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