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 & rdgsmaxzsim,
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
>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
>>
>.
>|||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:
> 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
>>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 ,
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?
>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:
>> 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
>>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
>>
>>.
>.
>|||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...
> 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?
>>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:
>> 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
>>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 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
>news:7eb301c4957f$53730a90$a301280a@.phx.gbl...
>> 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?
>>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:
>> 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
>>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
>>
>>.
>>.
>
>.
>|||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...
> 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
>>news:7eb301c4957f$53730a90$a301280a@.phx.gbl...
>> 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?
>>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:
>> 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
>>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 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
>news:06f301c49583$6ecb5410$a401280a@.phx.gbl...
>> 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
>>news:7eb301c4957f$53730a90$a301280a@.phx.gbl...
>> 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?
>>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:
>> 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
>>>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
>>>
>>>
>>>
>>>.
>>>
>>.
>>
>>.
>
>.
>|||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...
> 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
>>news:06f301c49583$6ecb5410$a401280a@.phx.gbl...
>> 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
>>news:7eb301c4957f$53730a90$a301280a@.phx.gbl...
>> 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?
>>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:
>>> 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
>>>
>>>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
>>>
>>>
>>>
>>>.
>>>
>>.
>>
>>.
>>
>>.|||>--Original Message--
>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
>
>.
>