Max file group per database in SQL Server 2005.

Hi All,
Whatâ's the max number of file group in SQL Server 2005 database? I knew that
in SQL Server 2000, the max file group per database is 16.
Regards,
Chen"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:81540E0C-F76D-462E-9C4B-42E0C683D63E@.microsoft.com...
> Hi All,
> What's the max number of file group in SQL Server 2005 database? I knew
> that
> in SQL Server 2000, the max file group per database is 16.
> Regards,
> Chen
>
BOL is your friend:
http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx
According to my 2000 BOL the maximum was 256 in 2000 as well. I can't say
I've ever put those limits to the test.
It is often recommended that you should aim to have the same number of files
as you have processors. On that basis you would be unlikely to need as many
as 256 filegroups.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Actually there were 256 in SQL2000. In 2005 there are 32,767.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/13e95046-0e76-4604-b561-d1a74dd824d7.htm
--
Andrew J. Kelly SQL MVP
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:81540E0C-F76D-462E-9C4B-42E0C683D63E@.microsoft.com...
> Hi All,
> Whatâ?Ts the max number of file group in SQL Server 2005 database? I knew
> that
> in SQL Server 2000, the max file group per database is 16.
> Regards,
> Chen
>|||From Books Online (2005): 32,767
The 2000 Books Online claims that max number of filegroups per database is 256. Did you try to
create more than 16? Either we have an error in Books Online, a bug in the product or perhaps you
was misinformed?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:81540E0C-F76D-462E-9C4B-42E0C683D63E@.microsoft.com...
> Hi All,
> Whatâ's the max number of file group in SQL Server 2005 database? I knew that
> in SQL Server 2000, the max file group per database is 16.
> Regards,
> Chen
>|||On Mon, 23 Apr 2007 20:24:41 +0100, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:
>http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx
>According to my 2000 BOL the maximum was 256 in 2000 as well. I can't say
>I've ever put those limits to the test.
>It is often recommended that you should aim to have the same number of files
>as you have processors. On that basis you would be unlikely to need as many
>as 256 filegroups.
In regards to the processors, I would point out that this should be
read as relating to the number of processors for *active* filegroups,
you may want some more to hold archive stuff, and to facilitate
backups, to support partitioned tables, and to map to different
classes of storage (RAID 1,5,10).
All those good reasons, and I've never really played much with it
myself!
Josh