none
database file size best practice

    Question

  • Is there a document for SQL server database file size best practise? Many people suggest keep file size under 64 GB. I coudl not find the document.

    Many thanks,

    SkyRiver


    Thank you Skiiiiii

    Monday, July 23, 2012 5:47 PM

Answers

All replies

  • Hi,

    I've never seen a 64GB recommendation.  Do you have a source?

    This article briefly talks about initially sizing data files.  http://msdn.microsoft.com/en-us/library/ms190970(SQL.105).aspx



    Thanks, Andrew

    Tuesday, July 24, 2012 2:28 PM
  • I agree with Andrew!
    May we know the source of such wrong information. There might be some context there.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Tuesday, July 24, 2012 2:42 PM
  • Hi,

    I think what you are referring to is the recommended disk partition alignment best practices where the recommended cluster size is 64kb (this must be done on an empty drive before files are on as its done through the format function). The reason for this is down to the underpinnings of SQL and how it grows with pages and extents in 64kb chunks.

    This article is a great read and shows how you can improve the IO performance as your DB files wont be crossing sectors and this more efficient reads.

    http://msdn.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

    Hope that helps!

     

    • Proposed as answer by SQLSpecialist Tuesday, July 24, 2012 7:58 PM
    Tuesday, July 24, 2012 7:58 PM
  • Well, I see the thought has been around for a while:

    http://serverfault.com/questions/72314/sql-server-2005-max-mdb-file-size

    (2009)

    But the consensus is that if it is some kind of guideline, it is often violated, I know I have.

    I vaguely recall some discussions about keeping filesize below some number like that as a best practice for some obscure NTFS reasons, or something, which might or might not apply on SANs, or in the latest versions of Windows, etc.  Hey back when I was a lad, we had to walk miles through the snow for 64 *megabytes*. 

    Josh

    Tuesday, July 24, 2012 9:31 PM