You must Sign In to post a response.
  • Category: Windows 8

    SQL server/ LDF and MDF file ratio

    Is there any standard ratios suggested for LDF and MDF files in MS SQL server, for better performance
  • #768113
    Hi Sathish,

    As per my understanding on SQL Server for better performance Check the following points.

    1. MDF should be more than LDF. If you are looking for ratio kind something like 85%-MDF and 15%-LDF
    2. Your file growth rate is important in fixing this ratio. Higher file growth higher MDF
    3. Hope you heard about RAID concepts, We need to keep RAID -5 or RAID -10 for MDF
    4. RAID -0 or RAID -1 for LDF for the better performance of the server.


  • #768120
    The recommended best practice is to assign a SQL Server Transaction log file its' very own disk or LUN.
    This is to avoid fragmentation of the transaction log file on disk, as other posters have mentioned, and to also avoid/minimise disk contention.
    The ideal scenario is to have your DBA allocate sufficient log space for your database environment ahead of time i.e. to allocate say x GB of data in one go. On a dedicated disk this will create a contiguous allocation, thereby avoiding fragmentation.
    If you need to grow your transaction log, again you should endeavour to do so in sizeable chunks in order to endeavour to allocate contiguously.

    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • Sign In to post your comments