June 20, 2012 at 5:35 am
Thanks for the question. I thought it was too easy to be true at first.
As for the "depends on the file system" consideration, "maximum database size in SQL Server 2012" seemed to me to mean the limits imposed within SQL Server 2012 itself. We are always constrained, with all applications, to the physical limits of our storage system, and file system peculiarities. So while the discussion of file system limits and merits is interesting, I don't see how it relates to the limits imposed by the DBMS itself.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
June 20, 2012 at 6:21 am
Hugo Kornelis (6/20/2012)
tommyh (6/19/2012)
If one wants be really picky "Depends on File System" is actually the right answer.You can install 2012 on FAT32 and that have a limit to 4GB/file. The 524,272TB limit is based on 32,767 * 16TB files. Since FAT32 cant hold a 16TB file the maximum limit on a FAT32 system would be 131068GB. (4GB-1B*32767)
Yeah, by that logic it also depends on the size of the installed disk. ...
This is true, these are normally factors you wouldn't consider in a question of this nature... except that the additional factor of file system was made part of the answer which to me makes it part of the equation.
EDIT: slight grammar correction.
June 20, 2012 at 6:46 am
I dislike ambiguous questions like this. I would like to see someone install a 524TB DB on a 3TB drive
June 20, 2012 at 6:49 am
So the real question to me is whether the 524 PB limit is imposed by SQL Server regardless of the file system in use. If so, shouldn't that be the "correct" answer? Storage size isn't really relevant as you can always increase your storage size if you have the money. However, if SQL Server itself doesn't limit the size, then maybe "Depends on file system" really is the correct answer.
June 20, 2012 at 6:57 am
tommyh (6/19/2012)
PS Not the answer i chose... i justed picked an answer at random since my own answer would be "More then i will ever need, so who cares" 😀
That just reminds me of one of my favorite quotes from my childhood. "640K ought to be enough for anybody." (Even if folks deny ever saying it.)
Looking at the Extended Support dates yesterday, I noticed that SQL 2012 had a sunset date in 2022. Who knows what folks will be stuffing in my old SQL database servers by then. 😉 (Some silly developers refuse to upgrade their applications to run with the more current RDMS. *eyes.his.multiple.SQL2000.boxes*)
June 20, 2012 at 7:05 am
Ernie Schlangen (6/20/2012)
So the real question to me is whether the 524 PB limit is imposed by SQL Server regardless of the file system in use. If so, shouldn't that be the "correct" answer? Storage size isn't really relevant as you can always increase your storage size if you have the money. However, if SQL Server itself doesn't limit the size, then maybe "Depends on file system" really is the correct answer.
SQL Server itself does impose this limit. As specified at the link included in the explanation, SQL Server has a limitation of 32767 (the maximum value in a signed short integer) files per database, and each of those files has a maximum size of 16 TB for data files or 2 TB for log files.
(I'll gladly run a test to confirm this, if you give me the budget to buy 524 PB of storage :D)
June 20, 2012 at 7:10 am
Hugo Kornelis (6/20/2012)
SQL Server itself does impose this limit. As specified at the link included in the explanation, SQL Server has a limitation of 32767 (the maximum value in a signed short integer) files per database, and each of those files has a maximum size of 16 TB for data files or 2 TB for log files.
Something's just occurred to me. Since at least one file in a database has to be the log file, wouldn't that make the actual limit 32766*16 + 2 = 524,258 TB?
June 20, 2012 at 7:17 am
Hugo Kornelis (6/20/2012)
Ernie Schlangen (6/20/2012)
So the real question to me is whether the 524 PB limit is imposed by SQL Server regardless of the file system in use. If so, shouldn't that be the "correct" answer? Storage size isn't really relevant as you can always increase your storage size if you have the money. However, if SQL Server itself doesn't limit the size, then maybe "Depends on file system" really is the correct answer.SQL Server itself does impose this limit. As specified at the link included in the explanation, SQL Server has a limitation of 32767 (the maximum value in a signed short integer) files per database, and each of those files has a maximum size of 16 TB for data files or 2 TB for log files.
(I'll gladly run a test to confirm this, if you give me the budget to buy 524 PB of storage :D)
Thanks for the answer and I'd love to help you out on the test, but if I had the money to build that, I would probably not be sitting here in the office looking at this huge stack of paper right now!:-P
June 20, 2012 at 7:20 am
This is true, these are normally factors you wouldn't consider in a question of this nature... except that the additional factor of file system was made part of the answer which to me makes it part of the equation.
But the fact is that the question itself asked for the maximum size. It did not ask based upon or if you have. Just simply what is the maximum size.
I see nothing to complain about or debate, unless I guess, I read into the question more than is there and then got it wrong;-)
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
June 20, 2012 at 7:22 am
640K ought to be enough for anybody
Scary, I remember saying that as well. I had a choice of 512KB or 640KB for a computer. I wanted to make sure that I never outgrew it so I went with 640:-) Same for the hard drive, doing development work I wanted that 30MB hard drive so that I would never run out.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
June 20, 2012 at 7:31 am
paul.knibbs (6/20/2012)
Hugo Kornelis (6/20/2012)
SQL Server itself does impose this limit. As specified at the link included in the explanation, SQL Server has a limitation of 32767 (the maximum value in a signed short integer) files per database, and each of those files has a maximum size of 16 TB for data files or 2 TB for log files.
Something's just occurred to me. Since at least one file in a database has to be the log file, wouldn't that make the actual limit 32766*16 + 2 = 524,258 TB?
That occurred to me as well. And then I recaclled having a similar discussion before. So I googled, and found this discussion on the MSDN support forums.
Apparently, this is a mistake that made its way in the SQL 2008 edition of Books Online and has not yet been corrected. Maybe someone should file a documentation bug?
For the record, I did "ask around" (as promised in my last post to the MSDN discussion), but I can't remember getting a satisfactory reply. My received email archhive doesn't go back far enough to check, so I have to go by memory here.
June 20, 2012 at 7:57 am
tommyh (6/20/2012)
Personally i wouldnt use FAT32 for anything today./T
FAT32 is widely used in USB Flash drives, portable drives and even on cell phone memory cards. You may be using it even if you don't realize you're doing so.:-)
Personally I think that other than portable storage, FAT32 shouldn't be used anywhere else, specially in production systems.
I also agree with the comments regarding the "maximum file size". If we know that the storage is limited by the file system, then for sure we know what the maximum file size would be, but if we had unlimited storage space we would not be sure why our production system starts failing when the database size reached 524Pb :-D. These questions should include a legend reading "if you had an unlimited storage system for your databases..."
"El" Jerry.
June 20, 2012 at 8:32 am
EL Jerry (6/20/2012)
These questions should include a legend reading "if you had an unlimited storage system for your databases..."
There is that slight chance that my Sharepoint users would see that and bring it to me saying, 'Hey! They have it. Why can't you make that available to ME?' (Of course, many of them already BELIEVE the unlimited storage system is available. They don't understand that the overflow is redirected to the bit bucket.) (And it's Sharepoint that makes me wonder if >500 PB databases won't be reached. :w00t:)
June 20, 2012 at 8:34 am
Koen Verbeeck (6/20/2012)
paul.knibbs (6/20/2012)
a kilobyte has always been 1024 bytes as far as I'm concerned.For me, a kilobyte is still 1000 bytes. kilo = 1000
Sure. If a prospective employer offers you say $80k, you do not expect $81,920...
June 20, 2012 at 8:49 am
Revenant (6/20/2012)
Koen Verbeeck (6/20/2012)
paul.knibbs (6/20/2012)
a kilobyte has always been 1024 bytes as far as I'm concerned.For me, a kilobyte is still 1000 bytes. kilo = 1000
Sure. If a prospective employer offers you say $80k, you do not expect $81,920...
However, if a prospective employer using MS SQL Server asks you to calculate file size, you should probably use the definition Microsoft has standardized on, which is 1024 bytes.
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply