none
Maximum Database Size in 2005\2008\R2\Denali 524,258 vs 524,272

    Question

  • Hi,
    Below are the maximum database size specification for each version of SQL Server.
     I'm not getting the values correct for 2008/R2/Denali by doing the basic math.
    Database can only have 32767 files including the data and log files. I tested this out in test environment. 
    Msg 5033, Level 16, State 1, Line 1
    The maximum of 32767 files per database has been exceeded.
    So max number of data files per database is 32766. Data file can grow only 16 tb and a Log file can grow only 2 tb..
    Now 32766 *16 + 2 = 524258. Why the specification say maximum db size as 524272 ?
    Just want to figure out what I missed here!!!! Any help is appreciated
    Krishnaraj
    SQL DBA

    Krishnaraj
    Thursday, October 27, 2011 2:54 PM

All replies

  • Hi Krishnaraj,

    Database can only have 32767 files including the data and log files. I tested this out in test environment. 


    Msg 5033, Level 16, State 1, Line 1
    The maximum of 32767 files per database has been exceeded.

    So max number of data files per database is 32766.

    No, the maximum number is 32767. Once that number is exceeded (i.e.,
    you try to allocate file 32768), you get this error message.

     Data file can grow only 16 tb and a Log file can grow only 2 tb..
    Now 32766 *16 + 2 = 524258. Why the specification say maximum db size as 524272 ?

    Log file does not count as part of the database size. The maximum size
    is therefor equal to the maximum number of data files (32767) * the
    maximum size of a data file (16 TB). 32767 * 16 = 524272.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Sunday, October 30, 2011 2:44 PM
  • Hugo,

    Thanks for the reply...Your reply raises some more questions and doubts in my mind

    So can we create a database with 32767 data files ? 

    "Database size doesn't include log file size"....Hmmmm

    Database_size in system stored procedure sp_spaceused returns size of the current database in megabytes. database_size includes both data and log files in that aspect.

    Kirshnaraj

     


    Krishnaraj
    Sunday, October 30, 2011 3:16 PM
  • Hi Kirshnaraj

    So can we create a database with 32767 data files ? 

    I didn't try it (frankly I don't think I would ever want to do this),
    but according to both the documentation and the error message you
    quoted in your first question, you can.

    "Database size doesn't include log file size"....Hmmmm

    Database_size in system stored procedure sp_spaceused returns size of the current database in megabytes. database_size includes both data and log files in that aspect.

    The purpose of sp_spaceused is, as the name implies, to give you an
    overview of the amount of space used for your database. The log file
    does use (take up) disk space, so it makes sense to include the log
    file in the sp_spaceused report.

    But the log file does not contain any actual data; it holds a log of
    changes that can be used to roll changes back or forward when needed
    (e.g. for recovery or to perform a rollback). For the purpose of the
    maximum database size of a SQL Server Express database, the log file
    is not counted, so the free edition really supports up to 10GB of
    data. I guessed that this would work the same for the maximum
    supported database size for enterprise edition, and when I tried the
    numbers matched, so my guess was confirmed.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Sunday, October 30, 2011 3:31 PM
  • Hi Hugo,

    So can we create a database with 32767 data files ?

    I didn't try it (frankly I don't think I would ever want to do this),
    but according to both the documentation and the error message you
    quoted in your first question, you can.

    You can't, unless you are able to create a database with out a log file..please read my first post again..

    That's what I tested out and that's why I asked for confirmation with this post regarding the total DB Size.

    Regarding counting log file size when considering the database size is a different topic.Let us not go back to basics of log file and you have a valid point in the express edition DB size explanation . To add up.. the database log file size in Express edition is not limited to the 10 GB size limitation as well.

    Either way I just want to see this number 524272 TB come up after a possible calculation of max size. 

    Krishnaraj


    Krishnaraj
    Sunday, October 30, 2011 6:39 PM
  • >*You can't, unless you are able to create a database with out a log file..please read my first post again..*

    Hi Krishnaraj,

    You are right, I did not read your first post well enough.

    I think that this is a simple oversight by the documentation writers,
    but I'll ask around and see if an anyone has an explanation.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Sunday, October 30, 2011 8:54 PM