Full Backup size bigger than .mdf file

Posted on 2011-10-18
Last Modified: 2012-08-14
Hi All, I have a database with .mdf file size under 2 GB and .ldf is right now 37GB for some reason.

When I took a full backup on the database, I surprised to see  the backup file to be 39GB when the .mdf file is only 2gb.

I though full backup will create a checkpoint and will flush out all the commited transactions in the .ldf to .mdf so that all the commited trasaction on teh .mdf file will be backed up and backup file size will reflect the size of .mdf file size.

but it proves wrong as the backup i had is the sum of .mdf and .ldf.

is that true, will the full backup size be the sum of size of bth .mdf and .ldf?
and why do backup include the un commited transactions in the .ldf files?

Question by:msdba
    LVL 59

    Accepted Solution

    It sounds like you are not doing any transaction log backups which is why it is constantly growing. In addition, remember when transactions are committed, that does not mean the log file is shrunk. So if you have a large transaction that grows the TLog to 37GB then releases everything after, you will will be 37GB just 99% free. :)

    What is your current RECOVERY MODEL?

    Author Comment

    Thanks for the comment.
    It is Full recovery model.

    but I guess full backup is about backing up the data in .mdf file, right?
    LVL 59

    Assisted Solution

    by:Kevin Cross
    Yes, in Full Recovery model, the expectation is that you are doing a FULL BACKUP and then frequent T-LOG BACKUPS. To restore point-in-time, you are restoring the .BAK and then the .TRN files up to the point in time needed.

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now