By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,143 Members | 1,908 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,143 IT Pros & Developers. It's quick & easy.

PRIMARY Files

P: n/a
101
Taking a course on SQL. They are saying you can get better performance by
having multiple files for a group.

They then graphically show an example of "Primary" with multiple data files.

I have tried altering PRIMARY to have multiple data files and I get and
error. I have tried creating a new database with multiple PRIMARY files and
get an error.

I can ALTER and CREATE secondary files with multiple data files with no
problem.

Am I mixing apples with oranges, does their "Primary" mean something
different then "PRIMARY"?

Looking at help it seems that you can only have one PRIMARY data file and I
am thinking their use of "Primary" means the primary group where you will
have your tables, not the PRIMARY group. Just don't want to lock onto the
wrong concept.

Thank you
Jul 23 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
101 wrote:
Taking a course on SQL. They are saying you can get better performance by
having multiple files for a group.

They then graphically show an example of "Primary" with multiple data files.

I have tried altering PRIMARY to have multiple data files and I get and
error. I have tried creating a new database with multiple PRIMARY files and
get an error.

I can ALTER and CREATE secondary files with multiple data files with no
problem.

Am I mixing apples with oranges, does their "Primary" mean something
different then "PRIMARY"?

Looking at help it seems that you can only have one PRIMARY data file and I
am thinking their use of "Primary" means the primary group where you will
have your tables, not the PRIMARY group. Just don't want to lock onto the
wrong concept.


Read the BOL article "Creating Filegroups." There can be only ONE
Primary file group. There are 3 types of file groups: Primary, User
Defined, Default (usually the Primary file group).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Jul 23 '05 #2

P: n/a
101
Oops, I think I got it. Each time you create a database you can specify the
location of it's PRIMARY file. Each database in an instance can have it's
PRIMARY data file pointing to a different data file. Therefore the PRIMARY
group can have multiple data files. But you can't have multiple data files
for a single database in the PRIMARY group.

Am I warm?
"101" <Ac********@yahoo.com> wrote in message
news:a9******************@twister.nyroc.rr.com...
Taking a course on SQL. They are saying you can get better performance by
having multiple files for a group.

They then graphically show an example of "Primary" with multiple data
files.

I have tried altering PRIMARY to have multiple data files and I get and
error. I have tried creating a new database with multiple PRIMARY files
and get an error.

I can ALTER and CREATE secondary files with multiple data files with no
problem.

Am I mixing apples with oranges, does their "Primary" mean something
different then "PRIMARY"?

Looking at help it seems that you can only have one PRIMARY data file and
I am thinking their use of "Primary" means the primary group where you
will have your tables, not the PRIMARY group. Just don't want to lock onto
the wrong concept.

Thank you

Jul 23 '05 #3

P: n/a
101 wrote:
I understand that there can be only one PRIMARY group. Where I am getting confused is how many data files can there be for one database within the PRIMARY group.

I am thinking I can have this:
MyDb_Primary 1 d:\mssql\data\MyDB_Pri.mdf PRIMARY 640 KB Unlimited 10% data only
MyDB_FG_Dat1 3 e:\mssql\data\MyDB_FG1_1.ndf MyDB_FG1 1024 KB Unlimited 10% data only
MyDB_FG_Dat2 4 f:\mssql\data\MyDB_FG2_2.ndf MyDB_FG1 1024 KB Unlimited 10% data only

But I can't have this:
MyDb_Prim_1 1 d:\mssql\data\MyDB_Pri_1.mdf PRIMARY 640 KB Unlimited 10% data only
MyDB_Prim_2 3 e:\mssql\data\MyDB_Pri_2.ndf PRIMARY 1024 KB Unlimited 10% data only
MyDB_Prim_3 4 f:\mssql\data\MyDB_Pri_3.ndf PRIMARY 1024 KB Unlimited 10% data only


That is my understanding also.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Jul 23 '05 #4

P: n/a
Hi,

I believe what you are looking for is the following command which adds
another file to the PRIMARY filegroup:

ALTER DATABASE FileGroupTest
ADD FILE
(
NAME = FileGroupTest2,
FILENAME = 'c:\FileGroupTestData2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)

This is a cut n paste from Books Online.

Jul 23 '05 #5

P: n/a
101 (Ac********@yahoo.com) writes:
Oops, I think I got it. Each time you create a database you can specify
the location of it's PRIMARY file. Each database in an instance can have
it's PRIMARY data file pointing to a different data file. Therefore the
PRIMARY group can have multiple data files. But you can't have multiple
data files for a single database in the PRIMARY group.


No, that's not correct. Filegroups do not span databases. In fact
there is no storage entity in SQL Server 7 and later which spans databases.
(In SQL 6.5 and earlier there was, as you always created databases on
devices.)

This is it: a database has one primary file and one primary file group.
The primary file group can contain several files, but only one is the
primary file. The primary file contains sysfiles, which holds information
about all other files and filegroups in the database. (At least this is
my understanding, after reading Kalen Delaney's "Inside SQL Server 2000".)

Here is an example that creates multiple files in the primary file group:

CREATE DATABASE multifile ON
(NAME = multifile_prim1,
filename = 'F:\mssql\data\multifile_1.mdf'),
(NAME = multifile_prim2,
filename = 'F:\mssql\data\multifile_2.mdf'),
FILEGROUP SECONDARY
(NAME = multifile_sec1,
filename = 'F:\mssql\data\multifile_1.ndf'),
(NAME = multifile_sec2,
filename = 'F:\mssql\data\multifile_2.ndf')
LOG ON
(NAME = multifile_log1,
filename = 'F:\mssql\data\multifile_1.ldf'),
(NAME = multifile_log2,
filename = 'F:\mssql\data\multifile_2.ldf')
go
exec sp_helpdb multifile

Note that the syntax in Books Online is apparently wrong. It goes:

CREATE DATABASE database_name
[ ON
[ < filespec > [ ,...n ] ]
[ , < filegroup > [ ,...n ] ]
]
[ LOG ON { < filespec > [ ,...n ] } ]
[ COLLATE collation_name ]
[ FOR LOAD | FOR ATTACH ]

< filespec > ::=
[ PRIMARY ]
( [ NAME = logical_file_name , ]
FILENAME = 'os_file_name'
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] ) [ ,...n ]

< filegroup > ::=

FILEGROUP filegroup_name < filespec > [ ,...n ]

But you cannot have FILEGROUP directly after ON. And you cannot use
PRIMARY in a <filespec> which is part of a FILEGROUP definition.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6

P: n/a
101
Ok,
I guess I was paying the learning sin-tax. I was trying this:
ALTER DATABASE FileGroupTest
ADD FILE
( NAME = FGT_Pri5,
FILENAME ='c:\mssql\data\FGT_Pri5.ndf'
)
TO FILEGROUP PRIMARY
Which of course errors. I guess the rule is if adding to the PRIMARY group
you don't use the FILEGROUP statement, it will default to the PRIMARY group.
You only use the FILEGROUP statement when adding a file to a user group.

Thank you
"Malcolm" <ma***********@innovartis.co.uk> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Hi,

I believe what you are looking for is the following command which adds
another file to the PRIMARY filegroup:

ALTER DATABASE FileGroupTest
ADD FILE
(
NAME = FileGroupTest2,
FILENAME = 'c:\FileGroupTestData2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)

This is a cut n paste from Books Online.

Jul 23 '05 #7

P: n/a
101
Ok, thank you. My problem was I had incorrect syntax trying to add files to
the PRIMARY group.

My understanding is one reason to have multiple files within a file group is
to allow SQL to stripe the data.

Now northwind has only one file (besides the log), northwind.mdf. So
sysfiles and along with everything else reside in that one file. What
happens when I add files to the PRIMARY group for the database?

a) sysfiles stay on northwind.mdf and the rest of the data is spread accross
northwind.mdf, northwind2.ndf, northwind3.ndf.

or

b) sysfiles stay on northwind.mdf and everything else is spread accross
northwind2.ndf and northwind3.ndf.

or

c) ??

Also

I notices that the second file you defined for primary had an extention of
..mdf, is that the common practice? .mdf files defined to the primary group
and .ndf files get defined in user groups?
I was defining the first file allocated to a group as .mdf and subsequent
files as .ndf regardless if they were in the PRIMARY group or a USER group.
It looks like you can do both, then again I haven't gone far enough to get
bitten. Even if you can get away with both practices, is there a common
practice for when you define a file with a .mdf extention and a .ndf
extention?

Don't mean to be a pain, just interested in the right way of doing things
and following good procedures.
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
101 (Ac********@yahoo.com) writes:
Oops, I think I got it. Each time you create a database you can specify
the location of it's PRIMARY file. Each database in an instance can have
it's PRIMARY data file pointing to a different data file. Therefore the
PRIMARY group can have multiple data files. But you can't have multiple
data files for a single database in the PRIMARY group.


No, that's not correct. Filegroups do not span databases. In fact
there is no storage entity in SQL Server 7 and later which spans
databases.
(In SQL 6.5 and earlier there was, as you always created databases on
devices.)

This is it: a database has one primary file and one primary file group.
The primary file group can contain several files, but only one is the
primary file. The primary file contains sysfiles, which holds information
about all other files and filegroups in the database. (At least this is
my understanding, after reading Kalen Delaney's "Inside SQL Server 2000".)

Here is an example that creates multiple files in the primary file group:

CREATE DATABASE multifile ON
(NAME = multifile_prim1,
filename = 'F:\mssql\data\multifile_1.mdf'),
(NAME = multifile_prim2,
filename = 'F:\mssql\data\multifile_2.mdf'),
FILEGROUP SECONDARY
(NAME = multifile_sec1,
filename = 'F:\mssql\data\multifile_1.ndf'),
(NAME = multifile_sec2,
filename = 'F:\mssql\data\multifile_2.ndf')
LOG ON
(NAME = multifile_log1,
filename = 'F:\mssql\data\multifile_1.ldf'),
(NAME = multifile_log2,
filename = 'F:\mssql\data\multifile_2.ldf')
go
exec sp_helpdb multifile

Note that the syntax in Books Online is apparently wrong. It goes:

CREATE DATABASE database_name
[ ON
[ < filespec > [ ,...n ] ]
[ , < filegroup > [ ,...n ] ]
]
[ LOG ON { < filespec > [ ,...n ] } ]
[ COLLATE collation_name ]
[ FOR LOAD | FOR ATTACH ]

< filespec > ::=
[ PRIMARY ]
( [ NAME = logical_file_name , ]
FILENAME = 'os_file_name'
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] ) [ ,...n ]

< filegroup > ::=

FILEGROUP filegroup_name < filespec > [ ,...n ]

But you cannot have FILEGROUP directly after ON. And you cannot use
PRIMARY in a <filespec> which is part of a FILEGROUP definition.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #8

P: n/a
101 (Ac********@yahoo.com) writes:
My understanding is one reason to have multiple files within a file
group is to allow SQL to stripe the data.
Hm, yes, but striping is probably best done by hardware.

Kalen Delaney discusses this in her book a bit, and she puts more
stress on flexibility. If you have a 60 GB database in one file and
you need to restore it, you need to find 60 GB of free space on one
disk. If you have three files, you can combine space on more than
one disk.
Now northwind has only one file (besides the log), northwind.mdf. So
sysfiles and along with everything else reside in that one file. What
happens when I add files to the PRIMARY group for the database?

a) sysfiles stay on northwind.mdf and the rest of the data is spread
accross northwind.mdf, northwind2.ndf, northwind3.ndf.
As I understand it, all system tables are in the primary file. The
user table and indexes are spread over the other filers, including
northwind.mdf.
I notices that the second file you defined for primary had an extention of
.mdf, is that the common practice? .mdf files defined to the primary group
and .ndf files get defined in user groups?
It appears that I've should have used .ndf for the second file, and
not .mdf. I rarely play with multiple files, so I just made a guess
that .ndf for files in other file groups, but I was wong.

In any case, that is just a convention and you can use .doc and .xls if
you feel like. (But I would not recommend using precisely those
exetentions!)
Even if you can get away with both practices, is there a common
practice for when you define a file with a .mdf extention and a .ndf
extention?


The practice appears to be .mdf for primary files and .ndf for secondary.
And .ldf for log files. But I would not be surprised if there are shops
where they have multiple files and they use .mdf for all data files.
I would suggest that the main thing here is that you is consistent, and
don't mix different styles. (I actually had this database with a log
file with .mdf. It caused me some problems when I tried to restore
a backup of the database in the SQL 2005 GUI, and I submitted a bug
report, because the GUI used the same file name for both. I thought
the GUI was crappy because it used .mdf for the log file. It took me
quite some time, to see that it was my own mistake.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #9

P: n/a
101
Thank you!
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
101 (Ac********@yahoo.com) writes:
My understanding is one reason to have multiple files within a file
group is to allow SQL to stripe the data.


Hm, yes, but striping is probably best done by hardware.

Kalen Delaney discusses this in her book a bit, and she puts more
stress on flexibility. If you have a 60 GB database in one file and
you need to restore it, you need to find 60 GB of free space on one
disk. If you have three files, you can combine space on more than
one disk.
Now northwind has only one file (besides the log), northwind.mdf. So
sysfiles and along with everything else reside in that one file. What
happens when I add files to the PRIMARY group for the database?

a) sysfiles stay on northwind.mdf and the rest of the data is spread
accross northwind.mdf, northwind2.ndf, northwind3.ndf.


As I understand it, all system tables are in the primary file. The
user table and indexes are spread over the other filers, including
northwind.mdf.
I notices that the second file you defined for primary had an extention
of
.mdf, is that the common practice? .mdf files defined to the primary
group
and .ndf files get defined in user groups?


It appears that I've should have used .ndf for the second file, and
not .mdf. I rarely play with multiple files, so I just made a guess
that .ndf for files in other file groups, but I was wong.

In any case, that is just a convention and you can use .doc and .xls if
you feel like. (But I would not recommend using precisely those
exetentions!)
Even if you can get away with both practices, is there a common
practice for when you define a file with a .mdf extention and a .ndf
extention?


The practice appears to be .mdf for primary files and .ndf for secondary.
And .ldf for log files. But I would not be surprised if there are shops
where they have multiple files and they use .mdf for all data files.
I would suggest that the main thing here is that you is consistent, and
don't mix different styles. (I actually had this database with a log
file with .mdf. It caused me some problems when I tried to restore
a backup of the database in the SQL 2005 GUI, and I submitted a bug
report, because the GUI used the same file name for both. I thought
the GUI was crappy because it used .mdf for the log file. It took me
quite some time, to see that it was my own mistake.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.