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

Question

Hi,

As per http://msdn.microsoft.com/en-us/library/ms143432.aspx

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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