[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2431
  • Last Modified:

MDF and LDF file size in SQL Server


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.
7 Solutions
Ephraim WangoyaCommented:

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

Anthony PerkinsCommented:
>>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.
This is only true if you are not doing proper maintenance on your log files. If properly maintained, tehy should never get too large.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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" ...

Alpesh PatelAssistant ConsultantCommented:
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.

<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 http://www.databasedesign-resource.com/sql-server-transaction-log.html
softblessAuthor Commented:

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now