October 27, 2011 at 1:01 pm
Hi,
As per http://msdn.microsoft.com/en-us/library/ms143432.aspx the maximum database size specification for
SQL Server 2005 - 524258
SQL Server 2008\R2\Denali - 524272
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..
Posted the same on MSDN --No Replies so far
October 27, 2011 at 2:28 pm
I'm going with typo.. And really, are you worried?
CEWII
October 27, 2011 at 2:42 pm
Elliott Whitlow (10/27/2011)
I'm going with typo.. And really, are you worried?CEWII
Definitely not a production issue 🙂
I saw same Max DB size mistake long back in the initial BOL version when 2005 came out, but that can be justified since the Max DB File size change from 32 TB to 16 TB. But a Typo in all 3 versions is little too much for such a product from MS. In fact I want to make sure, I'm not missing some thing in this basic calculation....
Any way thanks for the reply
October 27, 2011 at 3:30 pm
for SQL Server 2005:
Files per database : 32767 = 32766 data file + 1 log file
Max Database Size = 32766*16 TB + 1*2 TB = 524258 terabytes
MS SQL Server DBA
www.mehmetguzel.net
October 27, 2011 at 3:37 pm
If true for SQL Server 2008\R2\Denali - 524272.
This mean maybe max size of log file 16 TB
32,767*16 TB=524272
MS SQL Server DBA
www.mehmetguzel.net
October 27, 2011 at 3:45 pm
mehmetguzel (10/27/2011)
This mean maybe max size of log file 16 TB32,767*16 TB=524272
Max size of a log file is 2TB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2011 at 7:42 am
Well, technically speaking, I guess you can have more than one log file per database, although, I'm not sure if the combined size of these can be greater than 2TB (it may be an addressing limitation).
I can't see anywhere that they publish the maximum number of log files you can have, nor do I have space/inclination to find out where those limitations are, but could be something to do with that...
October 28, 2011 at 7:51 am
HowardW (10/28/2011)
Well, technically speaking, I guess you can have more than one log file per database, although, I'm not sure if the combined size of these can be greater than 2TB (it may be an addressing limitation).I can't see anywhere that they publish the maximum number of log files you can have, nor do I have space/inclination to find out where those limitations are, but could be something to do with that...
I tested out the max file limitation for a database..The maximum number of files per database is 32767 including log and data files. You can look at the error message I got after creating 32767 files in a database..
October 28, 2011 at 7:54 am
Krishnaraj9 (10/28/2011)
I tested out the max file limitation for a database..The maximum number of files per database is 32767 including log and data files. You can look at the error message I got after creating 32767 files in a database..
Ah, I see. That rules that out then. I thought the limitation was just for data files.
In which case, I'm going for a mistake, someone's just multiplied the max files by the max size of the data files and neglected to factor in the maximum size of the log file.
August 16, 2013 at 11:02 am
So the Max size remains the same as sql 2005 which is 524258 TB. May be a documentation error in MS143432. Thanks Krishnaraj!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply