Skip to Content

Database file sizes

Hi All,

Is there any specific guidelines on the size of datafiles in mssql?.

The best practices documents say you can maintain number of data files = the number of processors. When installing the SAP system it creates 3 data files by default. In production systems currently the size of these 3 files are very high.So is it a good option to restrict the growth of these files and add another 3 new datafiles and allow those files to grow.

regards,

dev

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Oct 14, 2010 at 01:10 PM

    In older DB systems, it was very important to limit the size of the data files. In some systems, like say Oracle on Windows, there was a hard requirement that datafiles not exceed 2GB. With SQLServer today and the availabilty of multi-core processors and SANs, really the biggest consideration is performance. It is not necessary to have more datafiles than CPU cores, in fact only having as many files as cores will make the administrator's job a little less cumbersome when it comes to growing them or system refreshes, etc.

    This is my standard approach:

    - Use at least 3 datafiles and 1 log file, but do not exceed number of CPU cores

    - Use autogrow, but periodically manage the file sizes: do not let one get much bigger than the others

    - Us a SAN, if possible, otherwise separate Log disk from Data disks, and spread out data read/write load

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Sukhdev Kaloor

      Yes, you can do that, but you will get alerts on the files that are full. To avoid those, you can also add a few extra files and go through the process of leveling them so that they are all roughly the same size, and leave them all plenty of space to grow.

  • Posted on Oct 14, 2010 at 12:27 PM

    Hi dev,

    there's a whitepaper published on Juergen Thomas' Blog ([http://blogs.msdn.com/b/saponsqlserver/archive/2009/06/24/new-sap-on-sql-server-2008-whitepaper-released.aspx]) that states the following:

    - Small sized systems, where 4 data files should be fine. These systems usually run on dedicated database servers that have around 4 cores.

    - Medium sized systems, where at least 8 data files are required. These systems usually run on dedicated database servers that have between 8 and 16 CPU cores.

    - Large sized systems where a minimum of 16 data files are required. These are usually systems that run on hardware that has between 16 and 32 CPU cores.

    - Xtra Large sized systems. Upcoming hardware over the next years certainly will support up to 256 CPU cores. However, we donu2019t necessarily expect a lot of customers deploying this kind of hardware for one dedicated database server, servicing one database of an SAP application. For XL systems we recommend 32 to 64 data files.

    For more information check out the whitepaper (its 404 currently which should be fixed soon)

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 14, 2010 at 12:52 PM

    Hi,

    It is not mandatory to have the number of files equal to that of the number of processors but it is a good practice to have better performance. In case if u have more number of datafiles (say 8) in a system having less number of CPU's(say 4), then u may experience a degradation in the performance during the peak hours.

    Hope this clarifies.

    Regards,

    Varadharajan M

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.