'PRIMARY' filegroup is full - no it's not!

I have a SQL Server 2000 database, in which the size of the primary
filegroup is 5 GB. The disk where the data resides has 14 GB of free
space ? that should be more than enough free space for a 5 GB
database, right?
The problem is that when I perform certain operations (esp. recreating
a clustered index), it still seems to run out of space with this
error:
Could not allocate space for object 'ActivityTracker' in database
'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
been terminated.
Here's where it gets weird: in the database properties "Automatically
grow file" IS checked, and Maximum file size is unrestricted. So
there's lots of free space (nearly 3X the size of the db), and the
growth is not restricted - what makes it think the filegroup is full?
I found a few newsgroup postings that suggested sometimes the
auto-grow cannot grow fast enough, so you should manually increase the
size of the database. I tried this, using "alter database" to raise
the size of the primary filegroup as high as 12GB ? more that twice
its original 5GB size ? but it still fails with the error message
above. And - here's another weird thing ? at the time the error
occurs, the database shrinks itself back down to 5 GB (even though I
do NOT have the "Auto shrink" property checked). What's going on
here?
I appreciate any ideas you can offer on why this database seems to run
out of space when there's plenty of space to be had.
RayTry running:
DBCC UPDATEUSAGE (0)
This will correct entries in sysindexes so that sp_spaceused will give you
the correct values for the amount of space actually used in the database.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ray.Net" <scott@.csgsolutions.com> wrote in message
news:10a1d257.0412050543.11b31a32@.posting.google.com...
I have a SQL Server 2000 database, in which the size of the primary
filegroup is 5 GB. The disk where the data resides has 14 GB of free
space - that should be more than enough free space for a 5 GB
database, right?
The problem is that when I perform certain operations (esp. recreating
a clustered index), it still seems to run out of space with this
error:
Could not allocate space for object 'ActivityTracker' in database
'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
been terminated.
Here's where it gets weird: in the database properties "Automatically
grow file" IS checked, and Maximum file size is unrestricted. So
there's lots of free space (nearly 3X the size of the db), and the
growth is not restricted - what makes it think the filegroup is full?
I found a few newsgroup postings that suggested sometimes the
auto-grow cannot grow fast enough, so you should manually increase the
size of the database. I tried this, using "alter database" to raise
the size of the primary filegroup as high as 12GB - more that twice
its original 5GB size - but it still fails with the error message
above. And - here's another weird thing - at the time the error
occurs, the database shrinks itself back down to 5 GB (even though I
do NOT have the "Auto shrink" property checked). What's going on
here?
I appreciate any ideas you can offer on why this database seems to run
out of space when there's plenty of space to be had.
Ray|||It's a good practice to pre-allocate enough space to handle anticipated
space requirements. Allow autogrow only as a safety net rather than allow
files to grow routinely during normal operation. This includes both normal
growth as well as space needed for maintenance.
Rebuilding a clustered index requires about 120% of the table size so you
should have free space of at least 1.2 times the largest table in your
database. As Tom suggested, you can use DBCC UPDATEUSAGE or sp_spaceused
@.updateusage=true to correct inaccurate space reporting.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray.Net" <scott@.csgsolutions.com> wrote in message
news:10a1d257.0412050543.11b31a32@.posting.google.com...
>I have a SQL Server 2000 database, in which the size of the primary
> filegroup is 5 GB. The disk where the data resides has 14 GB of free
> space - that should be more than enough free space for a 5 GB
> database, right?
> The problem is that when I perform certain operations (esp. recreating
> a clustered index), it still seems to run out of space with this
> error:
> Could not allocate space for object 'ActivityTracker' in database
> 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
> been terminated.
> Here's where it gets weird: in the database properties "Automatically
> grow file" IS checked, and Maximum file size is unrestricted. So
> there's lots of free space (nearly 3X the size of the db), and the
> growth is not restricted - what makes it think the filegroup is full?
> I found a few newsgroup postings that suggested sometimes the
> auto-grow cannot grow fast enough, so you should manually increase the
> size of the database. I tried this, using "alter database" to raise
> the size of the primary filegroup as high as 12GB - more that twice
> its original 5GB size - but it still fails with the error message
> above. And - here's another weird thing - at the time the error
> occurs, the database shrinks itself back down to 5 GB (even though I
> do NOT have the "Auto shrink" property checked). What's going on
> here?
> I appreciate any ideas you can offer on why this database seems to run
> out of space when there's plenty of space to be had.
> Ray|||Thank you for the quick replies ? these commands do help me get a
better picture of the true database size. However, things are still
not adding up properly. Please bear with me and I'll explain:
After running DBCC UPDATEUSAGE (0), the output of sp_spaceused is:
database_name database_size unallocated space
DataLoad 5081.06 MB 107.53 MB
reserved data index_size unused
4928224 KB 2632800 KB 2204624 KB 90800 KB
So, the db is approximately 5 GB in size. And when I look at the mdf
data file, it is indeed 5 GB.
According to sp_spaceused, my largest table is about 4 GB in size. So
using Dan's 120% rule, I need just under 5GB of free space, in
addition to the 5 GB already used, for a total of about 10 GB. Just
to allow more than enough margin for error, I went ahead and allocated
12 GB:
ALTER DATABASE DataLoad MODIFY FILE(NAME='DataLoad_Data',
SIZE=12000MB)
'DataLoad_Data' is the primary filegroup, and the only filegroup for
the database. And I can see that after running the ALTER DATABASE
command, the mdf file is now 12,288,000 KB in size, just as expected.
Question 1: sp_spaceused still gives exactly the same output.
Shouldn't it reflect this new larger 12 GB size somewhere in the
output ? I expected the "database_size" or "reserved" number to jump
up, but they didn't.
Question 2: Even with the database at this huge new size, my attempt
to rebuild an index fails with the "Could not allocate space" error,
and the mdf file has shriveled itself back down to 5 GB (or could
something be shrinking the file back down to 5 GB before the index
creation has finished, which would account for the error?). And I'm
not even rebuilding the index on the largest table -- the table whose
index I'm recreating only takes up a measly 500 MB. It's not
necessarily a problem with this particular index; I've also hit the
error on rebuilding a different index, and on a large INSERT
operation. Obviously I am missing something here ? any ideas?
For the record, here's how I'm attempting to recreate the index:
CREATE CLUSTERED INDEX idx_Exceptions_LoadID ON dbo.Exceptions
(LoadId)
WITH FILLFACTOR = 90, DROP_EXISTING ON [PRIMARY]
Thanks again for sharing your expertise!
Ray Scott
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<#u$VEzt2EHA.1564@.TK2MSFTNGP09.phx.gbl>...
> It's a good practice to pre-allocate enough space to handle anticipated
> space requirements. Allow autogrow only as a safety net rather than allow
> files to grow routinely during normal operation. This includes both normal
> growth as well as space needed for maintenance.
> Rebuilding a clustered index requires about 120% of the table size so you
> should have free space of at least 1.2 times the largest table in your
> database. As Tom suggested, you can use DBCC UPDATEUSAGE or sp_spaceused
> @.updateusage=true to correct inaccurate space reporting.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ray.Net" <scott@.csgsolutions.com> wrote in message
> news:10a1d257.0412050543.11b31a32@.posting.google.com...
> >I have a SQL Server 2000 database, in which the size of the primary
> > filegroup is 5 GB. The disk where the data resides has 14 GB of free
> > space - that should be more than enough free space for a 5 GB
> > database, right?
> >
> > The problem is that when I perform certain operations (esp. recreating
> > a clustered index), it still seems to run out of space with this
> > error:
> > Could not allocate space for object 'ActivityTracker' in database
> > 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
> > been terminated.
> >
> > Here's where it gets weird: in the database properties "Automatically
> > grow file" IS checked, and Maximum file size is unrestricted. So
> > there's lots of free space (nearly 3X the size of the db), and the
> > growth is not restricted - what makes it think the filegroup is full?
> >
> > I found a few newsgroup postings that suggested sometimes the
> > auto-grow cannot grow fast enough, so you should manually increase the
> > size of the database. I tried this, using "alter database" to raise
> > the size of the primary filegroup as high as 12GB - more that twice
> > its original 5GB size - but it still fails with the error message
> > above. And - here's another weird thing - at the time the error
> > occurs, the database shrinks itself back down to 5 GB (even though I
> > do NOT have the "Auto shrink" property checked). What's going on
> > here?
> >
> > I appreciate any ideas you can offer on why this database seems to run
> > out of space when there's plenty of space to be had.
> >
> > Ray|||The sp_spaceused proc reports space actually used within your database.
Thus, although the file size is 12GB, you're only using 5GB.
I suspect that you have auto-shrink turned on. Turn this off. It's one of
those 'features' that can come up and bite you in cases like this.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ray.Net" <scott@.csgsolutions.com> wrote in message
news:10a1d257.0412052210.609fb2c9@.posting.google.com...
Thank you for the quick replies - these commands do help me get a
better picture of the true database size. However, things are still
not adding up properly. Please bear with me and I'll explain:
After running DBCC UPDATEUSAGE (0), the output of sp_spaceused is:
database_name database_size unallocated space
DataLoad 5081.06 MB 107.53 MB
reserved data index_size unused
4928224 KB 2632800 KB 2204624 KB 90800 KB
So, the db is approximately 5 GB in size. And when I look at the mdf
data file, it is indeed 5 GB.
According to sp_spaceused, my largest table is about 4 GB in size. So
using Dan's 120% rule, I need just under 5GB of free space, in
addition to the 5 GB already used, for a total of about 10 GB. Just
to allow more than enough margin for error, I went ahead and allocated
12 GB:
ALTER DATABASE DataLoad MODIFY FILE(NAME='DataLoad_Data',
SIZE=12000MB)
'DataLoad_Data' is the primary filegroup, and the only filegroup for
the database. And I can see that after running the ALTER DATABASE
command, the mdf file is now 12,288,000 KB in size, just as expected.
Question 1: sp_spaceused still gives exactly the same output.
Shouldn't it reflect this new larger 12 GB size somewhere in the
output - I expected the "database_size" or "reserved" number to jump
up, but they didn't.
Question 2: Even with the database at this huge new size, my attempt
to rebuild an index fails with the "Could not allocate space" error,
and the mdf file has shriveled itself back down to 5 GB (or could
something be shrinking the file back down to 5 GB before the index
creation has finished, which would account for the error?). And I'm
not even rebuilding the index on the largest table -- the table whose
index I'm recreating only takes up a measly 500 MB. It's not
necessarily a problem with this particular index; I've also hit the
error on rebuilding a different index, and on a large INSERT
operation. Obviously I am missing something here - any ideas?
For the record, here's how I'm attempting to recreate the index:
CREATE CLUSTERED INDEX idx_Exceptions_LoadID ON dbo.Exceptions
(LoadId)
WITH FILLFACTOR = 90, DROP_EXISTING ON [PRIMARY]
Thanks again for sharing your expertise!
Ray Scott
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:<#u$VEzt2EHA.1564@.TK2MSFTNGP09.phx.gbl>...
> It's a good practice to pre-allocate enough space to handle anticipated
> space requirements. Allow autogrow only as a safety net rather than allow
> files to grow routinely during normal operation. This includes both
normal
> growth as well as space needed for maintenance.
> Rebuilding a clustered index requires about 120% of the table size so you
> should have free space of at least 1.2 times the largest table in your
> database. As Tom suggested, you can use DBCC UPDATEUSAGE or sp_spaceused
> @.updateusage=true to correct inaccurate space reporting.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ray.Net" <scott@.csgsolutions.com> wrote in message
> news:10a1d257.0412050543.11b31a32@.posting.google.com...
> >I have a SQL Server 2000 database, in which the size of the primary
> > filegroup is 5 GB. The disk where the data resides has 14 GB of free
> > space - that should be more than enough free space for a 5 GB
> > database, right?
> >
> > The problem is that when I perform certain operations (esp. recreating
> > a clustered index), it still seems to run out of space with this
> > error:
> > Could not allocate space for object 'ActivityTracker' in database
> > 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
> > been terminated.
> >
> > Here's where it gets weird: in the database properties "Automatically
> > grow file" IS checked, and Maximum file size is unrestricted. So
> > there's lots of free space (nearly 3X the size of the db), and the
> > growth is not restricted - what makes it think the filegroup is full?
> >
> > I found a few newsgroup postings that suggested sometimes the
> > auto-grow cannot grow fast enough, so you should manually increase the
> > size of the database. I tried this, using "alter database" to raise
> > the size of the primary filegroup as high as 12GB - more that twice
> > its original 5GB size - but it still fails with the error message
> > above. And - here's another weird thing - at the time the error
> > occurs, the database shrinks itself back down to 5 GB (even though I
> > do NOT have the "Auto shrink" property checked). What's going on
> > here?
> >
> > I appreciate any ideas you can offer on why this database seems to run
> > out of space when there's plenty of space to be had.
> >
> > Ray|||Tom is right about auto-shrink. I never turn on this option in production
and shrink manually if there is a decrease in estimated space requirements.
For best performance, it's best to pre-allocate the space needed.
With your single file group, you can calculate the available space in your
data files by subtracting the reserved space reported by sp_spaceused from
the sum of the data file sizes reported by sp_helpdb.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray.Net" <scott@.csgsolutions.com> wrote in message
news:10a1d257.0412052210.609fb2c9@.posting.google.com...
> Thank you for the quick replies - these commands do help me get a
> better picture of the true database size. However, things are still
> not adding up properly. Please bear with me and I'll explain:
> After running DBCC UPDATEUSAGE (0), the output of sp_spaceused is:
> database_name database_size unallocated space
> DataLoad 5081.06 MB 107.53 MB
> reserved data index_size unused
> 4928224 KB 2632800 KB 2204624 KB 90800 KB
> So, the db is approximately 5 GB in size. And when I look at the mdf
> data file, it is indeed 5 GB.
> According to sp_spaceused, my largest table is about 4 GB in size. So
> using Dan's 120% rule, I need just under 5GB of free space, in
> addition to the 5 GB already used, for a total of about 10 GB. Just
> to allow more than enough margin for error, I went ahead and allocated
> 12 GB:
> ALTER DATABASE DataLoad MODIFY FILE(NAME='DataLoad_Data',
> SIZE=12000MB)
> 'DataLoad_Data' is the primary filegroup, and the only filegroup for
> the database. And I can see that after running the ALTER DATABASE
> command, the mdf file is now 12,288,000 KB in size, just as expected.
> Question 1: sp_spaceused still gives exactly the same output.
> Shouldn't it reflect this new larger 12 GB size somewhere in the
> output - I expected the "database_size" or "reserved" number to jump
> up, but they didn't.
> Question 2: Even with the database at this huge new size, my attempt
> to rebuild an index fails with the "Could not allocate space" error,
> and the mdf file has shriveled itself back down to 5 GB (or could
> something be shrinking the file back down to 5 GB before the index
> creation has finished, which would account for the error?). And I'm
> not even rebuilding the index on the largest table -- the table whose
> index I'm recreating only takes up a measly 500 MB. It's not
> necessarily a problem with this particular index; I've also hit the
> error on rebuilding a different index, and on a large INSERT
> operation. Obviously I am missing something here - any ideas?
> For the record, here's how I'm attempting to recreate the index:
> CREATE CLUSTERED INDEX idx_Exceptions_LoadID ON dbo.Exceptions
> (LoadId)
> WITH FILLFACTOR = 90, DROP_EXISTING ON [PRIMARY]
> Thanks again for sharing your expertise!
> Ray Scott
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:<#u$VEzt2EHA.1564@.TK2MSFTNGP09.phx.gbl>...
>> It's a good practice to pre-allocate enough space to handle anticipated
>> space requirements. Allow autogrow only as a safety net rather than
>> allow
>> files to grow routinely during normal operation. This includes both
>> normal
>> growth as well as space needed for maintenance.
>> Rebuilding a clustered index requires about 120% of the table size so you
>> should have free space of at least 1.2 times the largest table in your
>> database. As Tom suggested, you can use DBCC UPDATEUSAGE or sp_spaceused
>> @.updateusage=true to correct inaccurate space reporting.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ray.Net" <scott@.csgsolutions.com> wrote in message
>> news:10a1d257.0412050543.11b31a32@.posting.google.com...
>> >I have a SQL Server 2000 database, in which the size of the primary
>> > filegroup is 5 GB. The disk where the data resides has 14 GB of free
>> > space - that should be more than enough free space for a 5 GB
>> > database, right?
>> >
>> > The problem is that when I perform certain operations (esp. recreating
>> > a clustered index), it still seems to run out of space with this
>> > error:
>> > Could not allocate space for object 'ActivityTracker' in database
>> > 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
>> > been terminated.
>> >
>> > Here's where it gets weird: in the database properties "Automatically
>> > grow file" IS checked, and Maximum file size is unrestricted. So
>> > there's lots of free space (nearly 3X the size of the db), and the
>> > growth is not restricted - what makes it think the filegroup is full?
>> >
>> > I found a few newsgroup postings that suggested sometimes the
>> > auto-grow cannot grow fast enough, so you should manually increase the
>> > size of the database. I tried this, using "alter database" to raise
>> > the size of the primary filegroup as high as 12GB - more that twice
>> > its original 5GB size - but it still fails with the error message
>> > above. And - here's another weird thing - at the time the error
>> > occurs, the database shrinks itself back down to 5 GB (even though I
>> > do NOT have the "Auto shrink" property checked). What's going on
>> > here?
>> >
>> > I appreciate any ideas you can offer on why this database seems to run
>> > out of space when there's plenty of space to be had.
>> >
>> > Ray|||We have having the same exact problem. There is 30+ GB Free on the Drive
(Dynamic Disk, by the way). Database is set to unrestricted file growth.
Everything looks fine with the configuration of the sql database...however,
the database thinks there is no free space on the drive. Very wierd!!
Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
ill be calling MS soon!!
Dale
txskibum@.hotmail.com
"Ray.Net" wrote:
> I have a SQL Server 2000 database, in which the size of the primary
> filegroup is 5 GB. The disk where the data resides has 14 GB of free
> space â' that should be more than enough free space for a 5 GB
> database, right?
> The problem is that when I perform certain operations (esp. recreating
> a clustered index), it still seems to run out of space with this
> error:
> Could not allocate space for object 'ActivityTracker' in database
> 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
> been terminated.
> Here's where it gets weird: in the database properties "Automatically
> grow file" IS checked, and Maximum file size is unrestricted. So
> there's lots of free space (nearly 3X the size of the db), and the
> growth is not restricted - what makes it think the filegroup is full?
> I found a few newsgroup postings that suggested sometimes the
> auto-grow cannot grow fast enough, so you should manually increase the
> size of the database. I tried this, using "alter database" to raise
> the size of the primary filegroup as high as 12GB â' more that twice
> its original 5GB size â' but it still fails with the error message
> above. And - here's another weird thing â' at the time the error
> occurs, the database shrinks itself back down to 5 GB (even though I
> do NOT have the "Auto shrink" property checked). What's going on
> here?
> I appreciate any ideas you can offer on why this database seems to run
> out of space when there's plenty of space to be had.
> Ray
>|||We have having the same exact problem. There is 30+ GB Free on the Drive
(Dynamic Disk, by the way). Database is set to unrestricted file growth.
Everything looks fine with the configuration of the sql database...however,
the database thinks there is no free space on the drive. Very wierd!!
Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
ill be calling MS soon!!
Dale
txskibum@.hotmail.com
"Ray.Net" wrote:
> I have a SQL Server 2000 database, in which the size of the primary
> filegroup is 5 GB. The disk where the data resides has 14 GB of free
> space â' that should be more than enough free space for a 5 GB
> database, right?
> The problem is that when I perform certain operations (esp. recreating
> a clustered index), it still seems to run out of space with this
> error:
> Could not allocate space for object 'ActivityTracker' in database
> 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
> been terminated.
> Here's where it gets weird: in the database properties "Automatically
> grow file" IS checked, and Maximum file size is unrestricted. So
> there's lots of free space (nearly 3X the size of the db), and the
> growth is not restricted - what makes it think the filegroup is full?
> I found a few newsgroup postings that suggested sometimes the
> auto-grow cannot grow fast enough, so you should manually increase the
> size of the database. I tried this, using "alter database" to raise
> the size of the primary filegroup as high as 12GB â' more that twice
> its original 5GB size â' but it still fails with the error message
> above. And - here's another weird thing â' at the time the error
> occurs, the database shrinks itself back down to 5 GB (even though I
> do NOT have the "Auto shrink" property checked). What's going on
> here?
> I appreciate any ideas you can offer on why this database seems to run
> out of space when there's plenty of space to be had.
> Ray
>|||Hi
Yes, you might have it on autogrow, but think about this:
Processes are filling your DB, your DB is virtually full, Autogrow kicks in,
Growing a DB by 10% does take a while, during this time, the last remainder
of the pages gets used up. Bingo, no more space as the DB is still growing.
Primary Group Full gets returned to your processes. The newly allocated
pages are not available for use until the DB has grown fully.
The lesson here. Grow your DB in advance of large operations so that you
don't run into this problem.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:64492BF2-BB58-48D5-A458-D1BE70FC9372@.microsoft.com...
> We have having the same exact problem. There is 30+ GB Free on the Drive
> (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> Everything looks fine with the configuration of the sql
database...however,
> the database thinks there is no free space on the drive. Very wierd!!
> Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks
like
> ill be calling MS soon!!
> Dale
> txskibum@.hotmail.com
>
> "Ray.Net" wrote:
> > I have a SQL Server 2000 database, in which the size of the primary
> > filegroup is 5 GB. The disk where the data resides has 14 GB of free
> > space - that should be more than enough free space for a 5 GB
> > database, right?
> >
> > The problem is that when I perform certain operations (esp. recreating
> > a clustered index), it still seems to run out of space with this
> > error:
> > Could not allocate space for object 'ActivityTracker' in database
> > 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
> > been terminated.
> >
> > Here's where it gets weird: in the database properties "Automatically
> > grow file" IS checked, and Maximum file size is unrestricted. So
> > there's lots of free space (nearly 3X the size of the db), and the
> > growth is not restricted - what makes it think the filegroup is full?
> >
> > I found a few newsgroup postings that suggested sometimes the
> > auto-grow cannot grow fast enough, so you should manually increase the
> > size of the database. I tried this, using "alter database" to raise
> > the size of the primary filegroup as high as 12GB - more that twice
> > its original 5GB size - but it still fails with the error message
> > above. And - here's another weird thing - at the time the error
> > occurs, the database shrinks itself back down to 5 GB (even though I
> > do NOT have the "Auto shrink" property checked). What's going on
> > here?
> >
> > I appreciate any ideas you can offer on why this database seems to run
> > out of space when there's plenty of space to be had.
> >
> > Ray
> >|||Thanks Mike! I will try this!!
Dale
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Yes, you might have it on autogrow, but think about this:
> Processes are filling your DB, your DB is virtually full, Autogrow kicks in,
> Growing a DB by 10% does take a while, during this time, the last remainder
> of the pages gets used up. Bingo, no more space as the DB is still growing.
> Primary Group Full gets returned to your processes. The newly allocated
> pages are not available for use until the DB has grown fully.
> The lesson here. Grow your DB in advance of large operations so that you
> don't run into this problem.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dale" <Dale@.discussions.microsoft.com> wrote in message
> news:64492BF2-BB58-48D5-A458-D1BE70FC9372@.microsoft.com...
> > We have having the same exact problem. There is 30+ GB Free on the Drive
> > (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> > Everything looks fine with the configuration of the sql
> database...however,
> > the database thinks there is no free space on the drive. Very wierd!!
> > Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks
> like
> > ill be calling MS soon!!
> >
> > Dale
> > txskibum@.hotmail.com
> >
> >
> >
> > "Ray.Net" wrote:
> >
> > > I have a SQL Server 2000 database, in which the size of the primary
> > > filegroup is 5 GB. The disk where the data resides has 14 GB of free
> > > space - that should be more than enough free space for a 5 GB
> > > database, right?
> > >
> > > The problem is that when I perform certain operations (esp. recreating
> > > a clustered index), it still seems to run out of space with this
> > > error:
> > > Could not allocate space for object 'ActivityTracker' in database
> > > 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
> > > been terminated.
> > >
> > > Here's where it gets weird: in the database properties "Automatically
> > > grow file" IS checked, and Maximum file size is unrestricted. So
> > > there's lots of free space (nearly 3X the size of the db), and the
> > > growth is not restricted - what makes it think the filegroup is full?
> > >
> > > I found a few newsgroup postings that suggested sometimes the
> > > auto-grow cannot grow fast enough, so you should manually increase the
> > > size of the database. I tried this, using "alter database" to raise
> > > the size of the primary filegroup as high as 12GB - more that twice
> > > its original 5GB size - but it still fails with the error message
> > > above. And - here's another weird thing - at the time the error
> > > occurs, the database shrinks itself back down to 5 GB (even though I
> > > do NOT have the "Auto shrink" property checked). What's going on
> > > here?
> > >
> > > I appreciate any ideas you can offer on why this database seems to run
> > > out of space when there's plenty of space to be had.
> > >
> > > Ray
> > >
>
>