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.
Best Answer
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.