Huge backups on a small database

  • This is weird. I have a database that is approximately 4 Gb, but it's daily backups are over 30 Gb each. The transaction logs are 39 Gb while it's backups average about 140 Mb. I tried to shrink the log, but it's only got 1 Mb of free space. Is this somehow a problem with the transaction logs not being properly truncated? How can I solve this so I don't have 30+ Gb of .bak files for only a 4 Gb database?

    Cheers,

    Tom

  • Is the database being logshipped or replicated by any chance?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • or one of the cause listed here :

    Factors That Can Delay Log Truncation

    http://msdn.microsoft.com/en-us/library/ms345414.aspx

  • It is not being log shipped, but I do have mirroring configured, though it has been suspended for some time.

  • You found your reason !

    log_reuse_wait = 5 ; DATABASE_MIRRORING

    Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database (full recovery model only).

    For more information, see "Database Mirroring and the Transaction Log," later in this topic.

  • Full backups will include some log records and they may have lots of yours because of the suspension. That's definitely the log issue.

    Remove mirroring or get it working again.

  • Yup. I saw that in the link you posted. That explains why the log is not being truncated for certain. Does that also explain why the full backups are nearly 10x the size of the actually database?

  • Thank you for your help. I have removed mirroring. I will watch and see what, if anything, this does for the full backups as well.

    Thanks again for your help!

    Tom

  • Don't forget to let us know if you solved your problem.

  • thotvedt (1/13/2012)


    Yup. I saw that in the link you posted. That explains why the log is not being truncated for certain. Does that also explain why the full backups are nearly 10x the size of the actually database?

    Yes it does because your full backup includes the log 🙂

    Jared
    CE - Microsoft

Viewing 10 posts - 1 through 9 (of 9 total)

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