PRIMARY Files

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 you101 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)|||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@.yahoo.com> wrote in message
news:a9yce.11088$XF3.8443@.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|||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)|||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.|||101 (AceMagoo61@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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.uk> wrote in message
news:1114849361.130062.105130@.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.|||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@.sommarskog.se> wrote in message
news:Xns9648B2C5E59D8Yazorman@.127.0.0.1...
> 101 (AceMagoo61@.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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||101 (AceMagoo61@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9648C89CCC942Yazorman@.127.0.0.1...
> 101 (AceMagoo61@.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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Primary File Group Full

I'm getting an error when I'm copying data into a table. The error is saying the the Cannot allocate space. Primary File Group Is Full. I've been unable to find any information on the error. Can someone please tell me what this means and how to go abo
ut correcting it?
Thanks
The Primary File Group is the default file group for a database. You will
need to expand one of the files in the primary group or add a new file to
this group.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:95030BF0-28D9-4D26-AE82-07F685DFA63B@.microsoft.com...
> I'm getting an error when I'm copying data into a table. The error is
saying the the Cannot allocate space. Primary File Group Is Full. I've
been unable to find any information on the error. Can someone please tell
me what this means and how to go about correcting it?
> Thanks
|||This is for tempdb, but much of it applies to all DBs
http://www.aspfaq.com/2446
http://www.aspfaq.com/
(Reverse address to reply.)
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:95030BF0-28D9-4D26-AE82-07F685DFA63B@.microsoft.com...
> I'm getting an error when I'm copying data into a table. The error is
saying the the Cannot allocate space. Primary File Group Is Full. I've
been unable to find any information on the error. Can someone please tell
me what this means and how to go about correcting it?
> Thanks
|||The best that I can tell all of my SQL files are part of the Primary File Group and all are set to "Automatically Grow File". Is there something else that needs to be done to avoid this issue?
Gary
"Geoff N. Hiten" wrote:

> The Primary File Group is the default file group for a database. You will
> need to expand one of the files in the primary group or add a new file to
> this group.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:95030BF0-28D9-4D26-AE82-07F685DFA63B@.microsoft.com...
> saying the the Cannot allocate space. Primary File Group Is Full. I've
> been unable to find any information on the error. Can someone please tell
> me what this means and how to go about correcting it?
>
>
|||Thanks, I'll look at this and see if it helps. I still feel a little lost on this one. All of my databases are set to "Automatically Grow" by 10% each time. I'm not sure why I should be getting this error message.
Gary
"Aaron [SQL Server MVP]" wrote:

> This is for tempdb, but much of it applies to all DBs
> http://www.aspfaq.com/2446
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:95030BF0-28D9-4D26-AE82-07F685DFA63B@.microsoft.com...
> saying the the Cannot allocate space. Primary File Group Is Full. I've
> been unable to find any information on the error. Can someone please tell
> me what this means and how to go about correcting it?
>
>
|||Because your disk doesn't have enough space for the file to autogrow?
http://www.aspfaq.com/
(Reverse address to reply.)
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:A5D86758-EC3A-475C-9466-409AD815B0BA@.microsoft.com...
> Thanks, I'll look at this and see if it helps. I still feel a little
lost on this one. All of my databases are set to "Automatically Grow" by
10% each time. I'm not sure why I should be getting this error message.[vbcol=seagreen]
> Gary
> "Aaron [SQL Server MVP]" wrote:
tell[vbcol=seagreen]
|||My C drive has 12gig available and my D drive has 93 gig.
My Master DB, TempDB DB, ect reside on my C drive and the project databases are on my D drive.
"Aaron [SQL Server MVP]" wrote:

> Because your disk doesn't have enough space for the file to autogrow?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:A5D86758-EC3A-475C-9466-409AD815B0BA@.microsoft.com...
> lost on this one. All of my databases are set to "Automatically Grow" by
> 10% each time. I'm not sure why I should be getting this error message.
> tell
>
>
|||Which database is causing this error? What is the current size (show us the
result of sp_spaceused)? Can you also show us the result of sp_helpfile?
http://www.aspfaq.com/
(Reverse address to reply.)
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:956F6496-E606-4550-A405-89B8C577EA8D@.microsoft.com...
> My C drive has 12gig available and my D drive has 93 gig.
> My Master DB, TempDB DB, ect reside on my C drive and the project
databases are on my D drive.
|||OK, here are the results.
sp_spaceused:
Database_name: ICEDataRep
Database_Size: 4490.88 mb
unallocated space: 615.58 MB
reserved: 3275376 KB
data: 3025336 KB
index_size: 11328 KB
unused: 238712
Let me add on other note here. There are many tables in this database and I only seem to get the error on one table. Here are the results:
sp_spaceused 'rClientActualData_arc'
rows: 32217618
reserved 2556216 KB
data: 2353224 KB
index_size: 8 KB
unused: 202984
Results of sp_helpfile
Datafile
Name: ICEDataRep_Data
FileID: 1
FileName: D:\SQL_Data\icedatarep.mdf
filegroup: PRIMARY
size 3905728 KB
maxsize: Unlimited
growth: 20%
usage: data only
Log File
Name: ICEDataRep_log
fileid: 2
Filename: D:\SQL_Data\icedatarep.ldf
filegroup: NULL
size: 692928 KB
maxsize: unlimited
growth: 10%
usage: log only
I'm going to continue to do other testing to see if I can isolate the problem.
Thanks
Gary
"Aaron [SQL Server MVP]" wrote:

> Which database is causing this error? What is the current size (show us the
> result of sp_spaceused)? Can you also show us the result of sp_helpfile?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:956F6496-E606-4550-A405-89B8C577EA8D@.microsoft.com...
> databases are on my D drive.
>
>
|||I have seen times were the file just couldn't grow fast enough and this
error appears. It seems to be a tricky timing issue. The trick to avoiding
issues associated with autogrowing of files is to not let it auto grow in
the first place. I see that you have a 20% growth setting on your primary
file. If that is a 4GB file it needs to allocate and format 800MB when it
grows. That can take quite a while depending on the hardware. The larger
the file the more you should not use % for the autogrow size. Set a fixed
MB instead so you know how long it takes to grow and is well within an
acceptable time. Better yet you should schedule a manual or programmatic
growth at a time when there is little activity to avoid autogrow altogether.
Andrew J. Kelly SQL MVP
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:A5F260BD-1AA6-446C-A37D-C7C26DA58195@.microsoft.com...
> OK, here are the results.
> sp_spaceused:
> Database_name: ICEDataRep
> Database_Size: 4490.88 mb
> unallocated space: 615.58 MB
> reserved: 3275376 KB
> data: 3025336 KB
> index_size: 11328 KB
> unused: 238712
> Let me add on other note here. There are many tables in this database and
I only seem to get the error on one table. Here are the results:
> sp_spaceused 'rClientActualData_arc'
> rows: 32217618
> reserved 2556216 KB
> data: 2353224 KB
> index_size: 8 KB
> unused: 202984
> Results of sp_helpfile
> Datafile
> Name: ICEDataRep_Data
> FileID: 1
> FileName: D:\SQL_Data\icedatarep.mdf
> filegroup: PRIMARY
> size 3905728 KB
> maxsize: Unlimited
> growth: 20%
> usage: data only
> Log File
> Name: ICEDataRep_log
> fileid: 2
> Filename: D:\SQL_Data\icedatarep.ldf
> filegroup: NULL
> size: 692928 KB
> maxsize: unlimited
> growth: 10%
> usage: log only
> I'm going to continue to do other testing to see if I can isolate the
problem.[vbcol=seagreen]
> Thanks
> Gary
> "Aaron [SQL Server MVP]" wrote:
the[vbcol=seagreen]
sp_helpfile?[vbcol=seagreen]

Primary File Group Full

I'm getting an error when I'm copying data into a table. The error is sayin
g the the Cannot allocate space. Primary File Group Is Full. I've been una
ble to find any information on the error. Can someone please tell me what t
his means and how to go abo
ut correcting it?
ThanksThe Primary File Group is the default file group for a database. You will
need to expand one of the files in the primary group or add a new file to
this group.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:95030BF0-28D9-4D26-AE82-07F685DFA63B@.microsoft.com...
> I'm getting an error when I'm copying data into a table. The error is
saying the the Cannot allocate space. Primary File Group Is Full. I've
been unable to find any information on the error. Can someone please tell
me what this means and how to go about correcting it?
> Thanks|||This is for tempdb, but much of it applies to all DBs
http://www.aspfaq.com/2446
http://www.aspfaq.com/
(Reverse address to reply.)
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:95030BF0-28D9-4D26-AE82-07F685DFA63B@.microsoft.com...
> I'm getting an error when I'm copying data into a table. The error is
saying the the Cannot allocate space. Primary File Group Is Full. I've
been unable to find any information on the error. Can someone please tell
me what this means and how to go about correcting it?
> Thanks|||The best that I can tell all of my SQL files are part of the Primary File Gr
oup and all are set to "Automatically Grow File". Is there something else t
hat needs to be done to avoid this issue?
Gary
"Geoff N. Hiten" wrote:

> The Primary File Group is the default file group for a database. You will
> need to expand one of the files in the primary group or add a new file to
> this group.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:95030BF0-28D9-4D26-AE82-07F685DFA63B@.microsoft.com...
> saying the the Cannot allocate space. Primary File Group Is Full. I've
> been unable to find any information on the error. Can someone please tell
> me what this means and how to go about correcting it?
>
>|||Thanks, I'll look at this and see if it helps. I still feel a little lost
on this one. All of my databases are set to "Automatically Grow" by 10% eac
h time. I'm not sure why I should be getting this error message.
Gary
"Aaron [SQL Server MVP]" wrote:

> This is for tempdb, but much of it applies to all DBs
> http://www.aspfaq.com/2446
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:95030BF0-28D9-4D26-AE82-07F685DFA63B@.microsoft.com...
> saying the the Cannot allocate space. Primary File Group Is Full. I've
> been unable to find any information on the error. Can someone please tell
> me what this means and how to go about correcting it?
>
>|||Because your disk doesn't have enough space for the file to autogrow?
http://www.aspfaq.com/
(Reverse address to reply.)
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:A5D86758-EC3A-475C-9466-409AD815B0BA@.microsoft.com...
> Thanks, I'll look at this and see if it helps. I still feel a little
lost on this one. All of my databases are set to "Automatically Grow" by
10% each time. I'm not sure why I should be getting this error message.[vbcol=seagreen]
> Gary
> "Aaron [SQL Server MVP]" wrote:
>
tell[vbcol=seagreen]|||My C drive has 12gig available and my D drive has 93 gig.
My Master DB, TempDB DB, ect reside on my C drive and the project databases
are on my D drive.
"Aaron [SQL Server MVP]" wrote:

> Because your disk doesn't have enough space for the file to autogrow?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:A5D86758-EC3A-475C-9466-409AD815B0BA@.microsoft.com...
> lost on this one. All of my databases are set to "Automatically Grow" by
> 10% each time. I'm not sure why I should be getting this error message.
> tell
>
>|||Which database is causing this error? What is the current size (show us the
result of sp_spaceused)? Can you also show us the result of sp_helpfile?
http://www.aspfaq.com/
(Reverse address to reply.)
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:956F6496-E606-4550-A405-89B8C577EA8D@.microsoft.com...
> My C drive has 12gig available and my D drive has 93 gig.
> My Master DB, TempDB DB, ect reside on my C drive and the project
databases are on my D drive.|||OK, here are the results.
sp_spaceused:
Database_name: ICEDataRep
Database_Size: 4490.88 mb
unallocated space: 615.58 MB
reserved: 3275376 KB
data: 3025336 KB
index_size: 11328 KB
unused: 238712
Let me add on other note here. There are many tables in this database and I
only seem to get the error on one table. Here are the results:
sp_spaceused 'rClientActualData_arc'
rows: 32217618
reserved 2556216 KB
data: 2353224 KB
index_size: 8 KB
unused: 202984
Results of sp_helpfile
Datafile
Name: ICEDataRep_Data
FileID: 1
FileName: D:\SQL_Data\icedatarep.mdf
filegroup: PRIMARY
size 3905728 KB
maxsize: Unlimited
growth: 20%
usage: data only
Log File
Name: ICEDataRep_log
fileid: 2
Filename: D:\SQL_Data\icedatarep.ldf
filegroup: NULL
size: 692928 KB
maxsize: unlimited
growth: 10%
usage: log only
I'm going to continue to do other testing to see if I can isolate the proble
m.
Thanks
Gary
"Aaron [SQL Server MVP]" wrote:

> Which database is causing this error? What is the current size (show us t
he
> result of sp_spaceused)? Can you also show us the result of sp_helpfile?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:956F6496-E606-4550-A405-89B8C577EA8D@.microsoft.com...
> databases are on my D drive.
>
>|||I have seen times were the file just couldn't grow fast enough and this
error appears. It seems to be a tricky timing issue. The trick to avoiding
issues associated with autogrowing of files is to not let it auto grow in
the first place. I see that you have a 20% growth setting on your primary
file. If that is a 4GB file it needs to allocate and format 800MB when it
grows. That can take quite a while depending on the hardware. The larger
the file the more you should not use % for the autogrow size. Set a fixed
MB instead so you know how long it takes to grow and is well within an
acceptable time. Better yet you should schedule a manual or programmatic
growth at a time when there is little activity to avoid autogrow altogether.
Andrew J. Kelly SQL MVP
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:A5F260BD-1AA6-446C-A37D-C7C26DA58195@.microsoft.com...
> OK, here are the results.
> sp_spaceused:
> Database_name: ICEDataRep
> Database_Size: 4490.88 mb
> unallocated space: 615.58 MB
> reserved: 3275376 KB
> data: 3025336 KB
> index_size: 11328 KB
> unused: 238712
> Let me add on other note here. There are many tables in this database and
I only seem to get the error on one table. Here are the results:
> sp_spaceused 'rClientActualData_arc'
> rows: 32217618
> reserved 2556216 KB
> data: 2353224 KB
> index_size: 8 KB
> unused: 202984
> Results of sp_helpfile
> Datafile
> Name: ICEDataRep_Data
> FileID: 1
> FileName: D:\SQL_Data\icedatarep.mdf
> filegroup: PRIMARY
> size 3905728 KB
> maxsize: Unlimited
> growth: 20%
> usage: data only
> Log File
> Name: ICEDataRep_log
> fileid: 2
> Filename: D:\SQL_Data\icedatarep.ldf
> filegroup: NULL
> size: 692928 KB
> maxsize: unlimited
> growth: 10%
> usage: log only
> I'm going to continue to do other testing to see if I can isolate the
problem.[vbcol=seagreen]
> Thanks
> Gary
> "Aaron [SQL Server MVP]" wrote:
>
the[vbcol=seagreen]
sp_helpfile?[vbcol=seagreen]

Previous version detected

I just dowmnloaded sql 2005. I have been trying to install sql 2005 install keeps saying a previous version exists cannot proceed three is no sql anywhere to be found. what do i do? The support to this point is non-existent typical MSFT if you are not the peron to help who is?

Can you go to Add/Remove Programs and let us know if you have any entries for SQL Server 2005 (including Native Client)? Do you have any other SQL Server entries in ARP?

Also, what version are you trying to install?

Thanks,

Sam Lester (MSFT)