Is there any rules between the LDF file size and MDF file size?



  • I am using SQL Server.

    I am creating a program to copy the data from one source database to a target database, and need to prepare enough disk space for the target database files, including MDF file and LDF file.

    Is there any rules between the MDF file size and the LDF file size so that I can make an estimation of the total free space required?



  • If you're copying all of the same data, from all of the same tables, in your source database to your target database, then use your existing source database's file sizes and the provisioned disk behind them as a benchmark for what you'll likely roughly need for your target database.

    Otherwise, Erik Darling wrote a good brief article regarding this in https://www.brentozar.com/archive/2016/02/no-but-really-how-big-should-my-log-file-be/ . To summarize, he recommends:

    A good STARTING POINT for your log file is twice the size of the largest index in your database, or 25% of the database size. Whichever is larger.

    He then explains, if your largest index is greater in size than 25% of your database size (MDF) then under the assumption you're doing maintenance such as index rebuilds, you'll need a good amount of space to accomplish that plus additional space for whatever else could be running simultaneously. Otherwise 25% of the MDF size is a decent starting point.

    For new databases, I personally use the rule of halves, and make my LDF half the size of my MDF, and I set both files growth rates to half of their sizes. For example if I have a good idea my database will hold 1 GB of data, I'll set the MDF to 1 GB with a 512 MB growth rate. Then I'll set my LDF to 512 MB with a 256 MB growth rate.

    Neither of these suggestions are perfect, the values are somewhat arbitrary, but I think are good enough starting points.

    The reason it's hard to predict a perfect answer for how to size your LDF is because it depends precisely on how many transactions you plan to run against the database between the timeframe of when your Transaction Log backups run, and / or how big the biggest transaction you may run will be. That's something hard enough for anyone to figure out proactively when provisioning their own database file sizes, let alone to try to figure it out for someone else's database.

    For example, if your target database copies all of the same exact data as your source database, but after it's copied, you run some additional transformations on the data that are transactionally heavy, then you'll find you'll likely need more space allocated to your target database's LDF than there is allocated to your source database's

    Or if for example, on your target database you scheduled your Transaction Log backups to be at a slower frequency than your source database, you may also find you need a larger log file (LDF) for your target database than your source database's.

    Best of luck!


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2