Files and FileGroups in SQL Server

You can go through the below links:-
http://www.confio.com/logicalread/sql-server-tempdb-best-practices-multiple-files-w01/#.UpgtLHnxuM8

Bandi, if this helps please login to Mark As Answer. | Alert Moderator

Dear Chandu,

There is no limit of adding the file to database.
It totally depending on you and below points.
1. You can create N number of files.
2. Memory size for each file to be used.

See my article on How to create and use the File and File Group in Sql Server 2008.

http://www.dotnetfunda.com/articles/show/2538/how-to-create-and-use-the-file-and-file-group-in-sql-server-2008
If this helps you towards the solution, click on MARK IT AS ANSWER

Happy Coding.

Bandi, if this helps please login to Mark As Answer. | Alert Moderator

Hi Mahesh,
There is limitation to add number of files to a database in SQL Server 2012.
Limitations and Restrictions
1) You cannot add or remove a file while a BACKUP statement is running.
2) A maximum of 32,767 files and 32,767 filegroups can be specified for each database.

reference:
http://technet.microsoft.com/en-us/library/ms189253(v=sql.110).aspx

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Bandi, if this helps please login to Mark As Answer. | Alert Moderator

>> decide the number of files for a database in SQL Server
Best practices when designing a filegroup configuration:

Option 1 : Use 2 filegroups
Data filegroup
Index filegroup
Option 2 : Use 3 filegroups
Read only tables filegroup
Read-write tables filegroup
Index filegroup
Option 3: Depends on large tables count (more than 3)
Read only tables filegroup
Read-write tables filegroup
Index filegroug
Key table 1 filegroup
Key table 2 filegroup
Key table 3 filegroup

Rerference:
http://www.mssqltips.com/sqlservertip/1112/filegroups-in-sql-server-2005/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Bandi, if this helps please login to Mark As Answer. | Alert Moderator