Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I recently went to a conference for a software product we utilize at my company. During one of the presentations they stated that users should not allow the individual .mdb files to grow above 64GB, and referenced a MS article as their source stating that you should create a second data file before allowing your database to grow this big. I do not have access to that link now, and I cannot find any information to support their claim. Has anyone heard anything like this before, according to msdn the maximum file sizes listed here do not support their statement in the least.

I do know that there are other concerns besides hard size limits such as time to copy to a new location etc, but all things being equal in our installation I would rather keep one data file unless there is a compelling reason to split it up.

share|improve this question
add comment

4 Answers

up vote 5 down vote accepted

First, SQL Server uses MDF, NDF, and LDF files. Access uses MDB files. Just be clear you're using SQL Server as the database and not Access.

According to the maximum capacity specifications, the file size limit is 16TB for data files (mdf, ldf) and 2TB for log files (ldf). http://msdn.microsoft.com/en-us/library/ms143432.aspx

The database size in total is 524TB. There are people running systems with 100+TB, so I think you're OK for now.

I have had SQL 2000 databases that were 700GB in size, with files that were over 100GB. You still have some room.

share|improve this answer
    
Good catch. I saw SQL server all over the place and totally missed the .mdb file extension. –  GregD Oct 8 '09 at 14:03
    
Gahh! I KNEW there was something off about the details but I just couldn't put my finger on it! –  Mark Henderson Oct 8 '09 at 20:28
    
Doh! Thanks for pointing that out, I did of course mean .mdf thanks. –  Charles Oct 9 '09 at 15:37
add comment

This indicates 16 terabytes as the max. Was their product some type of backup product for SQL?

share|improve this answer
    
wow, that's a reduction from sql server 7/2000 which is listed as 32TB msdn.microsoft.com/en-us/library/aa933149%28SQL.80%29.aspx –  Nick Kavadias Oct 8 '09 at 0:05
    
No, it is actually an LMS (Learning Management System) for a college. I thought the info was bogus but wanted to get some opinions about if it was a bad idea for some reason I didn't see. –  Charles Oct 8 '09 at 13:50
add comment

If that is true, we're totally stuffed. We have an MDB that's well and truly > 64gb.

That said, there are compelling reasons to split it up if you have massive tables that have easilly partitionable information (i.e. all records A-K on one disk, L-Z on another). I set this up many many years ago and I don't remember much about it at all, but it made sense in their situation.

share|improve this answer
    
Haha, that is kind of what I was wondering. Gee is there some kind of negative side effect that I missed to this? That being said ours is growing at a rate of about 1 to 2 GB a month so it won't be long before it is gargantuan. –  Charles Oct 8 '09 at 13:51
    
If you have say, 20 million records in a table, then partitioning the table may well be worth it, but only if the records go on different disks. If they were financial records, for example, you might choose to put records pre 1989 onto a 'slow' disk to free up space on your main drive, as 20-year-old records aren't likely to be queried very often. –  Mark Henderson Oct 8 '09 at 20:30
add comment

All the database files on my server are well over 64 Gigs. DB is about 1 TB over a few drives.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.