Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SQL Server 2005 filgroup and file size recommendations for large databases

Posted on 2009-02-20
Medium Priority
Last Modified: 2012-05-06
We are having a debate over how to manage and size our larger sql server databases.
One of our databses is expected to grow to over a 1TB and possibly hit 2TB largely in a single table! I have created filgroups and each files within is created and set to 20GB and no auto growth. We then add files to the filegroup periodically maintaining a 20% capacity. This will mean that we grow to  between 50 and 100 files in the filegroup. Is this a good plan?
Can anyone recommend or point me to some advice that can help inform us on general recommendations for how to manage file and filegroups for large databases and what is the recommended largest size for a file in a file group. This for both SQL 2005 and 2008.
Many thanks.
Question by:ner_1808
  • 4
  • 3
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 23690300
>This will mean that we grow to  between 50 and 100 files in the filegroup. Is this a good plan?
sounds good to me: files that are not too large, still with a reasonable amount of files per filegroup.

> what is the recommended largest size for a file in a file group.
the file sizing is a compromize between number of files to manage vs backup time. the larger the largest file(s), the longer the backup and restore times.

Author Comment

ID: 23691121
Thanks for that. What would be your advice on the largest a file should be allowed to grow to that still keeps things manageable?

Author Comment

ID: 24174545
I understand that it is a compremise! However, this does not really help me.
What I am after is some kind of indication of what constitutes an excessively large file size for a single database file in a file group and why?
Or what is considered an excessive number of files in a filegroup and why?
Or anyone who knows where I can get this information.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1500 total points
ID: 24176334
the problem is there is no single number to give as answer.
it all depends on other factors, and usually they are "defined" by the backup/restore strategy and constraints.
the larger a single file is, the longer it takes to back it up and obviously also to restore it.
which means, the largest file is the "weakest" item in the chain (=> if all files have the same size, backup should run and finish in parallel on "all" the files... )

example (from oracle real world scenario I had, but it's the same issue in sql server):
a backup for a database having 134 data files, each file had 4GB size (except some small system data files). we configured the backup to run on 16 files in parallel (into 4 tapes, hence 4 files streamed into 1 tape...)
so, the backup had to 9 times the time it took up 1 such "set" of files to back up (in our case, if I remember correctly, that where 6-7 hours.
which was fine for the customer.

if the customer had chosen to configure all the oracle database into 1 single file, it would take over 1 day to back it up (unacceptable, apart from the oracle performance issues...)

on the other hand, had we configured the data file with max size of 1 GB, for example, the backup would not have gained anything, as the +-15 minutes of less of pure data backup would have been lost on the overhead for the 4 times the data files overhead to be saved also => excessive.


Author Comment

ID: 24231707
Thanks for that Angellll. Thats' certainly a good strategy for increasing the backup speeds and will look at our capability to do something similar and give me some reasons not enlarge our files any more.
I appreciate that no single number and each situation is different, but your example is very useful for me. Our file sizes are already 20GB and I have a new DBA that has asked me why we don't double or tripple the size of our files.
It just does not feel right to me to make the files any larger on all kinds of management  and performance levels. My problem is I can not find any information of limits, sugestions or any reasons from Microsoft to justify my stance.
In your experience, what is the largest single file size have your seen in use?
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 24231825
>In your experience, what is the largest single file size have your seen in use?
I have seen 100GB files already, but exactly that customer was complaining about the large backup window, and it got split into 40GB files...

the only "hard" limits you have is the OS file system limits, anything else is "soft" limits, as explained above, by backup/restore constraints.
It's like the trainer of a football (or whatever) team:
you have a hard limit of x players in the team/on the field, and your choice which players that have to play depends on soft limits ... in football, that would be things like what opponent you have, how the players evolved lately etc..

Author Closing Comment

ID: 31549156
Thanks for that. It's good to have some confirmation of what I have planned.

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question