473,836 Members | 1,498 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Jul 23 '05 #1
9 6868
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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Jul 23 '05 #2
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********@yah oo.com> wrote in message
news:a9******** **********@twis ter.nyroc.rr.co m...
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
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\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


That is my understanding also.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Jul 23 '05 #4
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.

Jul 23 '05 #5
101 (Ac********@yah oo.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_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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
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.u k> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.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:\FileGroupTe stData2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)

This is a cut n paste from Books Online.

Jul 23 '05 #7
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****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...
101 (Ac********@yah oo.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_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, es****@sommarsk og.se

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

Jul 23 '05 #8
101 (Ac********@yah oo.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #9
101
Thank you!
"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...
101 (Ac********@yah oo.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****@sommarsk og.se

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

Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2190
by: Anthony Robinson | last post by:
I have a question with regads to placement of data files/indexes on multiple filegroups. Here is the current scenario: I have a database comprised of two filegroups - PRIMARY and INDEX. The PRIMARY filegroup is comprised of two files, one residing on the R drive and another residing on the O drive. The INDEX filegroup consists of a file on the S drive. The transaction logs reside on the T drive. The box itself has five individual drive...
17
49841
by: Philip Yale | last post by:
I'm probably going to get shot down with thousands of reasons for this, but I've never really heard or read a convincing explanation, so here goes ... Clustered indexes are more efficient at returning large numbers of records than non-clustered indexes. Agreed? (Assuming the NC index doesn't cover the query, of course) Since it's only possible to have one clustered index, why is this almost always used for the primary key, when by...
3
11024
by: Mike | last post by:
Hi! I also asked this question in C# group with no results: I have 2 datasets loaded with data from two xml files having the same schema. The files contain data from yesterday and today. I'd like to merge both datasets in such a way that the resulting dataset should have all the today's data overriding yestrerday's data. CATCH: the today's dataset contains only the daily changes (DELTA) and merging should not remove all unchanged...
7
2840
by: hank | last post by:
Hi All In the Circular Logging when the Primary Log file fill up, the database manager will creat a secondary log files for the transaction; when this transaction finished, the secondary log files still allocated in log directory; when all application disconnect from database or database reactive or database restart the secondary log files will be deleted from log directory. For each transaction log request, database manager always...
1
3388
by: Jamal | last post by:
I am working on binary files of struct ACTIONS I have a recursive qsort/mergesort hybrid that 1) i'm not a 100% sure works correctly 2) would like to convert to iteration Any comments or suggestion for improvements or conversion to iteration would be much appreciated
6
2888
by: Jamal | last post by:
I am working on binary files of struct ACTIONS I have a recursive qsort/mergesort hybrid that 1) i'm not a 100% sure works correctly 2) would like to convert to iteration Any comments or suggestion for improvements or conversion to iteration would be much appreciated
1
1495
by: Martin Schmid | last post by:
I am developing using Visual C#.net. It appears that my web projects create files in both "My Documents\Visual Studio Projects" and ".\inetpub\webroot". Does backing up these files ensure that my code is all backed up? Is there a backup/restore FAQ somewhere? -- Thanks, Martin Schmid, EIT, CCSA, MCDBA, MCSE
2
7590
by: Kvara prasad | last post by:
For creating a user database three important files named primary, secondary & transaction log files. so my question is 'what is the difference between primary & secondary files?' and is 'what is the necessity of secondary files?'
1
1123
by: Ben Keshet | last post by:
Hi Pythoneers, I have a question about a code I wrote with the help of someone. The code below copy a few lines from different files into one file. It works fine as it is given here and generates the new file 'pockets.out' correctly, but says:"....py returned exit code 0". However, if I add more values to 'receptor' (say, receptor = ) it gives an error: "Exception raised while running script". Can anyone please advice me? Why is it...
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10551
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10595
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9379
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7793
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6979
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5650
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4458
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4021
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.