'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.
Ray
Try 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.c om...
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.c om...
>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>...[vbcol=seagreen]
> 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.c om...
|||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.c om...
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[vbcol=seagreen]
> 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.c om...
|||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.c om...[vbcol=seagreen]
> 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>...
|||Thanks guys for the additional responses. It does seem to be
auto-shrinking, BUT auto shrink is not turned on. Here's how I
checked, maybe I'm looking in the wrong place? Enterprise manager,
right-clicked database, Properties, Options tab. In the Settings
section, the only checked properties are Auto update statistics, Auto
create statistics, and Torn page detection. The other settings,
including Auto shrink, are not checked. Strange, eh?
I'm still puzzled as to why SQL Server doesn't seem to recognize the new
large file size after I expand the file using "ALTER DATABASE DataLoad
MODIFY FILE(NAME='DataLoad_Data', SIZE=12000MB)". I do not see the
larger 12 GB file size reflected in either sp_helpdb or sp_spaceused -
these SPs still report the old 5 GB size.
Thanks again for your help,
Ray Scott
===============================================
From: Dan Guzman
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.c om...[vbcol=seagreen]
> 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>...
anticipated[vbcol=seagreen]
you[vbcol=seagreen]
your[vbcol=seagreen]
sp_spaceused[vbcol=seagreen]
free[vbcol=seagreen]
recreating[vbcol=seagreen]
has[vbcol=seagreen]
"Automatically[vbcol=seagreen]
full?[vbcol=seagreen]
the[vbcol=seagreen]
I[vbcol=seagreen]
run[vbcol=seagreen]
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||sp_helpdb should report the increased size immediately after the ALTER. If
it shrinks afterward, perhaps a maintenance plan is setup to 'remove unused
space from data files'.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray Scott" <scott@.csgsolutions.com> wrote in message
news:OalrpF62EHA.3504@.TK2MSFTNGP12.phx.gbl...
> Thanks guys for the additional responses. It does seem to be
> auto-shrinking, BUT auto shrink is not turned on. Here's how I
> checked, maybe I'm looking in the wrong place? Enterprise manager,
> right-clicked database, Properties, Options tab. In the Settings
> section, the only checked properties are Auto update statistics, Auto
> create statistics, and Torn page detection. The other settings,
> including Auto shrink, are not checked. Strange, eh?
> I'm still puzzled as to why SQL Server doesn't seem to recognize the new
> large file size after I expand the file using "ALTER DATABASE DataLoad
> MODIFY FILE(NAME='DataLoad_Data', SIZE=12000MB)". I do not see the
> larger 12 GB file size reflected in either sp_helpdb or sp_spaceused -
> these SPs still report the old 5 GB size.
> Thanks again for your help,
> Ray Scott
> ===============================================
> From: Dan Guzman
> 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.c om...
> anticipated
> you
> your
> sp_spaceused
> free
> recreating
> has
> "Automatically
> full?
> the
> I
> run
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Consider using sp_helpfile to report the sizes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eYJnuJ62EHA.4072@.TK2MSFTNGP10.phx.gbl...
sp_helpdb should report the increased size immediately after the ALTER. If
it shrinks afterward, perhaps a maintenance plan is setup to 'remove unused
space from data files'.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray Scott" <scott@.csgsolutions.com> wrote in message
news:OalrpF62EHA.3504@.TK2MSFTNGP12.phx.gbl...
> Thanks guys for the additional responses. It does seem to be
> auto-shrinking, BUT auto shrink is not turned on. Here's how I
> checked, maybe I'm looking in the wrong place? Enterprise manager,
> right-clicked database, Properties, Options tab. In the Settings
> section, the only checked properties are Auto update statistics, Auto
> create statistics, and Torn page detection. The other settings,
> including Auto shrink, are not checked. Strange, eh?
> I'm still puzzled as to why SQL Server doesn't seem to recognize the new
> large file size after I expand the file using "ALTER DATABASE DataLoad
> MODIFY FILE(NAME='DataLoad_Data', SIZE=12000MB)". I do not see the
> larger 12 GB file size reflected in either sp_helpdb or sp_spaceused -
> these SPs still report the old 5 GB size.
> Thanks again for your help,
> Ray Scott
> ===============================================
> From: Dan Guzman
> 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.c om...
> anticipated
> you
> your
> sp_spaceused
> free
> recreating
> has
> "Automatically
> full?
> the
> I
> run
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Use sp_dboption 'MyDatabase' to tell you whether or not the AUTOSHRINK is
REALLY set or not.
Use sp_spaceused @.updateusage = 'true' to get your current space allocations
synch'd up.
Make sure in your Database Maintenance Plan, Reorganize Database, you DO NOT
have the REMOVE UNUSED SPACE option checked.
Take a look at your SQL Agent Job steps and make sure you DO NOT have any
hand-coded sqlmaint.exe jobs created, especially ones with
the -RmUnusedSpace parameter set. Also look for any Shrink Jobs that
someone may have one-off'ed but scheduled it to be reoccuring.
When you run the ALTER DATABASE statement, run a Profiler Trace and keep it
running while you run the CREATE CLUSTERED INDEX WITH DROP EXISTING command
to see if there are any executed DBCC SHRINKDATABASE or DBCC SHRINKFILE
operations occurring.
Finally, you modify files not file groups. File Groups are collections of
files that grow with the porportional fill algorythm, not the File Group
itself, although, that is the error message you get when none of the
contained files within the File Group are allowed to grow.
Also, I am suspecting that you have more than one file and that, perhaps,
not all of them are allowed to grow. The sp_helpdb 'MyDatabase' command
will show you all of the existing files and contained filegroups. You need
to validate all of them.
Hope this helps.
Sincerely,
Anthony Thomas

"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eDR$Yt92EHA.2568@.TK2MSFTNGP11.phx.gbl...
Consider using sp_helpfile to report the sizes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eYJnuJ62EHA.4072@.TK2MSFTNGP10.phx.gbl...
sp_helpdb should report the increased size immediately after the ALTER. If
it shrinks afterward, perhaps a maintenance plan is setup to 'remove unused
space from data files'.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray Scott" <scott@.csgsolutions.com> wrote in message
news:OalrpF62EHA.3504@.TK2MSFTNGP12.phx.gbl...
> Thanks guys for the additional responses. It does seem to be
> auto-shrinking, BUT auto shrink is not turned on. Here's how I
> checked, maybe I'm looking in the wrong place? Enterprise manager,
> right-clicked database, Properties, Options tab. In the Settings
> section, the only checked properties are Auto update statistics, Auto
> create statistics, and Torn page detection. The other settings,
> including Auto shrink, are not checked. Strange, eh?
> I'm still puzzled as to why SQL Server doesn't seem to recognize the new
> large file size after I expand the file using "ALTER DATABASE DataLoad
> MODIFY FILE(NAME='DataLoad_Data', SIZE=12000MB)". I do not see the
> larger 12 GB file size reflected in either sp_helpdb or sp_spaceused -
> these SPs still report the old 5 GB size.
> Thanks again for your help,
> Ray Scott
> ===============================================
> From: Dan Guzman
> 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.c om...
> anticipated
> you
> your
> sp_spaceused
> free
> recreating
> has
> "Automatically
> full?
> the
> I
> run
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!