MDF and LDF file size in SQL Server

Posted on 2011-10-19
Last Modified: 2012-05-12

I've been asked to predict the size for MDF and LDF file. Which one is usually has bigger size? what is the ratio size between MDF and LDF?

Please help. thanks.
Question by:softbless
    LVL 32

    Accepted Solution


    You have to observe your database and record daily growth, then you have concrete data to estimate future size requirements.

    There is no ratio between mdf and ldf. You can manage the size of your ldf in various ways so that it does not grow "too much"

    One way is to schedule regular backups of the log
    The other is to set the database recovery mode to simple

    LVL 75

    Assisted Solution

    by:Anthony Perkins
    >>Which one is usually has bigger size? <<
    Typically the sum of all the Transaction logs (there can be more than one per database) will be greater than the sum of all the data files (also can be more than one), but that is not necessarily the case when you first start: The Transaction Log can be greater.
    LVL 9

    Assisted Solution

    This is only true if you are not doing proper maintenance on your log files. If properly maintained, tehy should never get too large.
    LVL 12

    Assisted Solution

    This is tricky business. Well I do it in this way.
    1.> Identify core tables in the database (say Users, Engagements, Projects etc). By core tables I mean tables that are causing significant growth in db.
    2.> Try to note the record count under these tables in last 4-5 months.
    3.> Try to note the size of db (size of LDF and MDF) in same last 4-5 months if possible.
    4.> Proportionate db size with record count in core tables and based on trend do the same projection for future months based on expected size in core tables.
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    you cannot really "predict" unless you do the exercice:
    create a database, with all the tables, and fill it with the (volume of ) data that is expected to contain after the time of period that you are expected to predict.
    so far for the .mdf files, in first step.

    then, you shall manage your table scripts in a way that they peform the inserts, updates and deletes in a similar manner to what will happen in real-life. in parallel, you need to implement the backup strategy, especially the transaction log backup, to make the db behave like in real-life

    that will give you the max -.ldf file size, but ALSO a eventually slighter .mdf file size, as tables, when there are updates and deletes, might require more space than if there are only inserts.

    tip: ensure you have a clustered index on each table that will see UPDATE and DELETE ... otherwise it will never "shrink" ...

    LVL 21

    Assisted Solution

    by:Alpesh Patel
    If you have set REcovery mode as simple then
    It depends on Data size. so MDF is bigger than LDF.

    If you have set REcovery mode as Full or Bulk then
    LDF is bigger than MDF.

    LVL 25

    Assisted Solution

    <Recovery mode FULL LDF is bigger than MDF>
    That's not correct.  The size of LDF is dependend on how you declare (initial size, growth, autoshrink), maintanance (recovery mode: transaction log backup in case of FULL) and how your database is used (transactions, many insert/update/deletes).
    If your database is full of static data then there will the LDF will be small, if your database is full of data that constantly changes then the log file (LDF) will be larger.

    This link can explain something what is going on in a LFD

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now