Max Data File Size?

Question

Max Data File Size?

alanneveu2

SSC Rookie

Points: 32
More actions
October 26, 2001 at 1:27 pm

#157677

Hi,
I've got a production database (SQL Server 2000 on Windows 2000 Server, SP2
with NTFS) that is growing rather quickly. It's 6 GB currently, and should
be twice this size in another 3 months with no forseeable stopping point
(that's big for me, anyway). The hard drive configuration is SCSI RAID 5
with an extra hot-swappable drive. This means there are actually 2 levels
of redundancy, so I'd say the hard drive configuration is optimized for
safety rather than speed. (If I had to reconfigure the 4 18 GB SCSI hard
drives again, I suppose I would just go with RAID 1 for speed with a single
level of redundancy, but the fact the SQL Server uses lazy writes could mean
that I wouldn't gain much in performance.)
Having said all that, this large database has just one large data file and
just one large log file. I need to know if this database at risk by using
just one file for each. Somehow it just feels like a single 12 GB file is
getting so large that it (the OS?) might have problems with it. I'm not
concerned with speed at this point, so I'm not asking about how to separate
the database into multiple files for performance reasons (the RAID 5 config
means I would not gain anything from doing that since the physical drives
are made to look like one large logical drive). I'm also not asking about
the size limits for NTFS volumes - I know they are so high that I'll never
reach them. I just would like some experienced DBA's to tell me that
they've seen single data files and log files go way up to the XXXXX GB range
with no problems at all, or else tell me about whatever problems they have
seen with single files that that may have been a result of size.
I suppose this issue did not come up so much back in the 6.5 days since the
files did not automatically grow, and also the hard disks were not so large
back then. But these days it's common to have 80 GB hard disks with SQL 7.0
or 2000 and just let them grow, grow, grow. I would like to know if there
are any problems with this approach.
Thanks in advance,
Alan Neveu, MCSD


Alan Neveu, MCSD 
Lead Architect, CTO 
Recruiternet.net 
alan.neveu@recruiternet.net 
voice - 207-774-1411 x409



Alan Neveu, MCSD 
Lead Architect, CTO 
Recruiternet.net 
alan.neveu@recruiternet.net 
voice - 207-774-1411 x409

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

Currently, I've seen single databases at collegues sites with single files over 13GB with no issues. I used to manage a v6.5 database that was 15GB with no issues from the OS (that was NT4).

Andy or Brian should be able to comment on larger databases.

Steve Jones

steve@dkranch.net

alanneveu2 SSC Rookie Points: 32 More actions · Answer 2

So, when your DB gets to 15 GB with a single data file and a single log file, are you perfectly comfortable with just leaving it as-is? Or is there something inside that says, "better add another data file..." even when your HD still has another 30 GB free?

Alan Neveu, MCSD

Lead Architect, CTO

Recruiternet.net

alan.neveu@recruiternet.net

voice - 207-774-1411 x409

Alan Neveu, MCSD 
Lead Architect, CTO 
Recruiternet.net 
alan.neveu@recruiternet.net 
voice - 207-774-1411 x409

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

Hey Alan - Long time no see! Actually I have two in excess of 18 Gig and one that is rapidly growing to the size of 35 Gig where it should become stable (2 years of data type of thing). I have all of these databases with one file for the data and one for the log - different disks. No problems as of yet. They can be a bit troublesome if you don't have a lot of spare disk space but, the only down side is the time required to do backups.

I am planning on splitting one of the databases into multiple files and hope to gain some performance with the fact that they would be on different drives / different controllers as well as splitting the query load through proper table distribution, but have not done so yet. I will keep you posted on the results I see there.

See you Sunday night!

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Andy Warren SSC Guru Points: 119902 More actions · Answer 4

I've got one that is 15g or so. I'm very comfortable with SQL having large files in fact I prefer NOT to use filegroups, at least until I get to the point that I can't get a full backup in the time available (Im a long way from that) or just dont have space on a drive set where I need to split it up.

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

If you are on RAID, I wouldn't worry. You can add drives and with most modern RAID controllers, expand the size on the fly, though the rebuild takes time.