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


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...

  • 3
    I was gonna ask all this but I can't think of a thing to add so +1 from me and +1 again (if I could) on account of this is exactly the thing he needs to think about. – jcolebrand Jul 5 '11 at 19:51
  • Thanks Aaron. I want to get notified if the size overshoots the limit. Can you let me know how this notification will work? – Vivek Jul 6 '11 at 5:25
  • 2
    You could set up SQL Agent alerts, use the performance condition alert, and the Databases object to alert on Data File/Log File size > xGB. But the alert won't stop the database from growing, only setting a max on file size will do that. – SqlACID Jul 6 '11 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.