PRIMARY Files

[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

[MGFoster]

Re: PRIMARY Files

101 wrote:[color=blue]
> 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.[/color]

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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[101]

Re: PRIMARY Files

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" <AceMagoo61@yah oo.com> wrote in message
news:a9yce.1108 8$XF3.8443@twis ter.nyroc.rr.co m...[color=blue]
> 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
>[/color]

[MGFoster]

Re: PRIMARY Files

101 wrote:[color=blue]
> 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\M yDB_Pri.mdf PRIMARY 640 KB Unlimited 10% data only
> MyDB_FG_Dat1 3 e:\mssql\data\M yDB_FG1_1.ndf MyDB_FG1 1024 KB Unlimited 10% data only
> MyDB_FG_Dat2 4 f:\mssql\data\M yDB_FG2_2.ndf MyDB_FG1 1024 KB Unlimited 10% data only
>
> But I can't have this:
> MyDb_Prim_1 1 d:\mssql\data\M yDB_Pri_1.mdf PRIMARY 640 KB Unlimited 10% data only
> MyDB_Prim_2 3 e:\mssql\data\M yDB_Pri_2.ndf PRIMARY 1024 KB Unlimited 10% data only
> MyDB_Prim_3 4 f:\mssql\data\M yDB_Pri_3.ndf PRIMARY 1024 KB Unlimited 10% data only[/color]

That is my understanding also.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Malcolm]

Re: PRIMARY Files

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:\FileGroupTe stData2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)

This is a cut n paste from Books Online.

[Erland Sommarskog]

Re: PRIMARY Files

101 (AceMagoo61@yah oo.com) writes:[color=blue]
> 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.[/color]

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_na me , ]
FILENAME = 'os_file_name'
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_incremen t ] ) [ ,...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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[101]

Re: PRIMARY Files

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" <malcolm.leach@ innovartis.co.u k> wrote in message
news:1114849361 .130062.105130@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> 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:\FileGroupTe stData2.ndf',
> SIZE = 5MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 5MB
> )
>
> This is a cut n paste from Books Online.
>[/color]

[101]

Re: PRIMARY Files

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" <esquel@sommars kog.se> wrote in message
news:Xns9648B2C 5E59D8Yazorman@ 127.0.0.1...[color=blue]
> 101 (AceMagoo61@yah oo.com) writes:[color=green]
>> 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.[/color]
>
> 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_na me , ]
> FILENAME = 'os_file_name'
> [ , SIZE = size ]
> [ , MAXSIZE = { max_size | UNLIMITED } ]
> [ , FILEGROWTH = growth_incremen t ] ) [ ,...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, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Erland Sommarskog]

Re: PRIMARY Files

101 (AceMagoo61@yah oo.com) writes:[color=blue]
> My understanding is one reason to have multiple files within a file
> group is to allow SQL to stripe the data.[/color]

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.
[color=blue]
> 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.[/color]

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.
[color=blue]
> 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?[/color]

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!)
[color=blue]
> 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?[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[101]

Re: PRIMARY Files

Thank you!
"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns9648C89 CCC942Yazorman@ 127.0.0.1...[color=blue]
> 101 (AceMagoo61@yah oo.com) writes:[color=green]
>> My understanding is one reason to have multiple files within a file
>> group is to allow SQL to stripe the data.[/color]
>
> 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.
>[color=green]
>> 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.[/color]
>
> 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.
>[color=green]
>> 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?[/color]
>
> 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!)
>[color=green]
>> 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?[/color]
>
> 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, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]