LDF and MDF file sizes for a dynamically created Database

Question

I am creating a dynamic database from C# code, and would like the database to have a fixed size (for example 5 GB). While creating the DB through code, I need to specify MDF and LDF max file sizes. What should be the ideal values? 2.5GB each or is their some other ratio?

I have to make sure that the total DB size does not go more than 5GB, so should I only worry about the MDF size (keeping it fixed at 5GB) and leave the LDF size to grow unlimited (I cant do that but if it hurts performance then I have to think about it)?

Thanks, Vivek

Answer 1

This all depends on what you want to happen when the database hits 5GB. Do you want users to get errors and the database to become unusable full stop, or do you just want to be notified?

It also depends on whether you want to consider the log size separately from the actual data. The log size will fluctuate based on a variety of factors, including recovery model, frequency of full/log/diff backups, avg/peak size of transactions, duration between begin transaction and commit/rollback, etc.

Finally, it depends on whether you want to be have to manage this proactively. If for any of the above reason your log reaches 5GB but you've decided you don't want to stop the database from working because of log usage, you'll have to intervene if the max log size is set to 5GB but the data itself really hasn't grown...