Difference between multiple primary and secondary files..

[developmental2@walla.com]

Hi all..!


If I want to split an SQL DB into several physical files (as its 500GB
disk ran out of space, won't even run shrinks any more, and we bought
another 500GB disk to add to the PC)
then what is the difference between:
Adding another File to the primary group which will reside on the new
group;
Adding another file in another group.
We do not want to set any db objects (Tables, indexes)
to a secondary file, as this will involve lengthy data moving
operations. We would like the DB to continue working from where it is
utilizing the added space in a contigous (striped) manner.

Will striping occur in both cases? as I understand striping it means
that our stuck SQL Server will awake back to life as it will now have
500GB more data for its DB, even though we haven't set any of its
objects (tables, indexes) to explicitly use the secondary NDF file on
the new disk?
or will it only utilize the new space if we set some objects to reside
on that NDF?

for example if we run large queries which crash now (due to lack of
space) when we add the second drive will they start to work as the
process will grow striped from the full drive to the new drive, even if
all the queries' source tables are all still set to the old drive?


Thanks for any replies?

[Erland Sommarskog]

Re: Difference between multiple primary and secondary files..

(developmental2 @walla.com) writes:[color=blue]
> If I want to split an SQL DB into several physical files (as its 500GB
> disk ran out of space, won't even run shrinks any more, and we bought
> another 500GB disk to add to the PC)
> then what is the difference between:
> Adding another File to the primary group which will reside on the new
> group;
> Adding another file in another group.[/color]

If you add another filegroup, you need to move objects, as objects
below to a filegroup. Since you don't want to that, you should add
a secondary file to the primary filegroup.

I don't have much experience of secondary files myself, but I would
expect SQL Server start to spill over the new file, as soon as it is
available.

If you want to have certainty, it could be a good idea to set up a
small-size test, before you go ahead with the big database.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx