Primary File Group full

Hi ,
While trying to run a create that creates a table , the
query analyzer complains that primary file group is full
When i right-click on the database properties , i could
see there are many data files all set to unrestricted
growth while some are totally full there are others that
have spaces left.
- Why is the system complaining that primary file group
is full ? how does all those data file works then ? i have
tot if 1 of the data file is full it'll move to the next
data file ?
- when i run a query to create table , all the many data
file , in which data file the tbl will be created in ?
- how can i check the data file of that table that it
belongs to ?
my database setup is something like this :
name location space allocated File Group
prod1 j:\mssql7\prod1.dat 1000MB(full) primary
prod2 k:\mssql7\prod2.dat 5000MB(2000MBFree) primary
prod3 l:\mssql\prod3.dat 3000MB(full) primary
thks & rdgs
maxzsim,
In the database where you are creating your table, how much space is
left? Right click on the database and select View->Taskpad. You've
probably filled up your data file and not allowed the file to autogrow.
To change autogrow settings, right click the database and select
properties, data files tab.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
maxzsim wrote:
> Hi ,
> While trying to run a create that creates a table , the
> query analyzer complains that primary file group is full
> When i right-click on the database properties , i could
> see there are many data files all set to unrestricted
> growth while some are totally full there are others that
> have spaces left.
> - Why is the system complaining that primary file group
> is full ? how does all those data file works then ? i have
> tot if 1 of the data file is full it'll move to the next
> data file ?
> - when i run a query to create table , all the many data
> file , in which data file the tbl will be created in ?
> - how can i check the data file of that table that it
> belongs to ?
> my database setup is something like this :
> name location space allocated File Group
> prod1 j:\mssql7\prod1.dat 1000MB(full) primary
> prod2 k:\mssql7\prod2.dat 5000MB(2000MBFree) primary
> prod3 l:\mssql\prod3.dat 3000MB(full) primary
> thks & rdgs
>
|||Hi Mark ,
in the database that i am creating , i have several data
files (prod1 , prod2 , prod3 etc ...) and some data file
is full BUT some are still free and have checked that all
have been set to auto grow
if there are so many data files, in which data file
would the new table be created in and how do i check for
current tables which datafile they are in ?
rdgs
>--Original Message--
>maxzsim,
>In the database where you are creating your table, how
much space is
>left? Right click on the database and select View-
>Taskpad. You've
>probably filled up your data file and not allowed the
file to autogrow.
>To change autogrow settings, right click the database and
select[vbcol=seagreen]
>properties, data files tab.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>maxzsim wrote:
the[vbcol=seagreen]
could[vbcol=seagreen]
that[vbcol=seagreen]
group[vbcol=seagreen]
have[vbcol=seagreen]
next[vbcol=seagreen]
data[vbcol=seagreen]
Group
>.
>
|||maxzsim,
SQL Server looks at all data files for a database and allocates space
(in extents) in a proportional manner to the data files. So if you have
twice as much space in one file free than another, SQL will allocate two
extents to the first, and one extent to the other.
Do you have enough space on the disk for the full files to autogrow?
This might prevent them from doing so.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
maxzsim wrote:[vbcol=seagreen]
> Hi Mark ,
> in the database that i am creating , i have several data
> files (prod1 , prod2 , prod3 etc ...) and some data file
> is full BUT some are still free and have checked that all
> have been set to auto grow
> if there are so many data files, in which data file
> would the new table be created in and how do i check for
> current tables which datafile they are in ?
> rdgs
>
> much space is
>
> file to autogrow.
>
> select
>
> the
>
> could
>
> that
>
> group
>
> have
>
> next
>
> data
>
> Group
|||Hi Mark ,
i have checked the disk space on the various disk for
the data files. Some disks is almost out of disk space but
some disks have few GB of disk space which i am sure it
shld be enough for that new tables
also i have tested in my test production server ,
there's was a few hundreds MB of space , but i got the
PRIMARY FILE GROUP is full message as well and when i
check the disk space again, it now have a few GB free !
how did that happen ?
thks
>--Original Message--
>maxzsim,
>SQL Server looks at all data files for a database and
allocates space
>(in extents) in a proportional manner to the data files.
So if you have
>twice as much space in one file free than another, SQL
will allocate two
>extents to the first, and one extent to the other.
>Do you have enough space on the disk for the full files
to autogrow?[vbcol=seagreen]
>This might prevent them from doing so.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>maxzsim wrote:
data[vbcol=seagreen]
file[vbcol=seagreen]
all[vbcol=seagreen]
for[vbcol=seagreen]
and[vbcol=seagreen]
full[vbcol=seagreen]
it[vbcol=seagreen]
primary
>.
>
|||SQL Server tries to grow as per the increment specified for the file (in percent or fixed). If that
amount of space isn't available, the grow will not occur. Perhaps this is what you see?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:7eb301c4957f$53730a90$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi Mark ,
> i have checked the disk space on the various disk for
> the data files. Some disks is almost out of disk space but
> some disks have few GB of disk space which i am sure it
> shld be enough for that new tables
> also i have tested in my test production server ,
> there's was a few hundreds MB of space , but i got the
> PRIMARY FILE GROUP is full message as well and when i
> check the disk space again, it now have a few GB free !
> how did that happen ?
> thks
> allocates space
> So if you have
> will allocate two
> to autogrow?
> data
> file
> all
> for
> and
> full
> it
> primary
|||Hi Tibor/Mark ,
i am quite postitive that it was a few hundreds MB and
after the primary filegroup full message occurs , the disk
space shoots up to few GB of disk space
in the event of primary file group full does SQL do any
truncation of logs or shrink db ?
rdgs
>--Original Message--
>SQL Server tries to grow as per the increment specified
for the file (in percent or fixed). If that
>amount of space isn't available, the grow will not occur.
Perhaps this is what you see?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:7eb301c4957f$53730a90$a301280a@.phx.gbl...
but[vbcol=seagreen]
table ,[vbcol=seagreen]
in ?[vbcol=seagreen]
primary[vbcol=seagreen]
primary
>
>.
>
|||Sometime autogrow doesn't happen fast enough, resulting in the error message. This might just be
such a case...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:06f301c49583$6ecb5410$a401280a@.phx.gbl...[vbcol=seagreen]
> Hi Tibor/Mark ,
> i am quite postitive that it was a few hundreds MB and
> after the primary filegroup full message occurs , the disk
> space shoots up to few GB of disk space
> in the event of primary file group full does SQL do any
> truncation of logs or shrink db ?
> rdgs
> for the file (in percent or fixed). If that
> Perhaps this is what you see?
> message
> but
> table ,
> in ?
> primary
> primary
|||Hi Tibor ,
hmmm.. ok but i still cannot understand the drive was
left wif a few hundreds MB , now it's few GB. there should
not be any space to autogrow.
I have checked the db's properties , it's set to
autoshrink.
usually how will autoshrink occur ? will primary
filegroup is full cause the autoshrink to occur ?
if from a few hundred MB after the db is being shrunk
it's left with few GB of disk space does that mean my db
is full of inactive entries ?
thks

>--Original Message--
>Sometime autogrow doesn't happen fast enough, resulting
in the error message. This might just be
>such a case...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:06f301c49583$6ecb5410$a401280a@.phx.gbl...
disk[vbcol=seagreen]
any[vbcol=seagreen]
occur.[vbcol=seagreen]
for[vbcol=seagreen]
it[vbcol=seagreen]
free ![vbcol=seagreen]
files.[vbcol=seagreen]
files[vbcol=seagreen]
several[vbcol=seagreen]
that[vbcol=seagreen]
file[vbcol=seagreen]
how[vbcol=seagreen]
the[vbcol=seagreen]
database[vbcol=seagreen]
unrestricted[vbcol=seagreen]
then ? i[vbcol=seagreen]
many[vbcol=seagreen]
that[vbcol=seagreen]
File
>
>.
>
|||You will normally always have empty space in a SQL Server database. SQL Server need working space,
quite simply. You might want top check out http://www.karaszi.com/SQLServer/info_dont_shrink.asp for
info regarding shrink...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:7e8101c4958e$ade028a0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hi Tibor ,
> hmmm.. ok but i still cannot understand the drive was
> left wif a few hundreds MB , now it's few GB. there should
> not be any space to autogrow.
> I have checked the db's properties , it's set to
> autoshrink.
> usually how will autoshrink occur ? will primary
> filegroup is full cause the autoshrink to occur ?
> if from a few hundred MB after the db is being shrunk
> it's left with few GB of disk space does that mean my db
> is full of inactive entries ?
> thks
>
> in the error message. This might just be
> message
> disk
> any
> occur.
> for
> it
> free !
> files.
> files
> several
> that
> file
> how
> the
> database
> unrestricted
> then ? i
> many
> that
> File