Help - "Could not allocate space for object as the secondary group is full" error

Hello Peers,
We have a database in SqlServer 2K with a following settings:
Primary Group Size: 1024MB
Secondary Group Size: 1023 MB
Transaction Log size: 236 MB
File Growth: 10%
Recover Model: Simple
Auto Shrink: OFF
Recovery Model: Simple
We have a table "TestTable' that has 400,000 records. All the indexes
for the table is on the Secondary group.
The problem is, when we alter a size of the column and save the
changes in EnterPriseManager, we get the error "Could not allocate
space for object 'TestTable' in database 'TestDb' because the
'Secondary' filegroup is full." We get the same error when we use
"Alter Column" command in Query Analyzer as well(We have run "Dbcc
ShrinkFile" before running the alter table command).
Could anyone throw some light on altering the size of columns on a
table with 400000+records without any problems like the one above?
TIA for any help.
Regards
Pradeep.L
On May 11, 11:56 am, pradee...@.hotmail.com wrote:
> Hello Peers,
> We have a database in SqlServer 2K with a following settings:
> Primary Group Size: 1024MB
> Secondary Group Size: 1023 MB
> Transaction Log size: 236 MB
> File Growth: 10%
> Recover Model: Simple
> Auto Shrink: OFF
> Recovery Model: Simple
> We have a table "TestTable' that has 400,000 records. All the indexes
> for the table is on the Secondary group.
> The problem is, when we alter a size of the column and save the
> changes in EnterPriseManager, we get the error "Could not allocate
> space for object 'TestTable' in database 'TestDb' because the
> 'Secondary' filegroup is full." We get the same error when we use
> "Alter Column" command in Query Analyzer as well(We have run "Dbcc
> ShrinkFile" before running the alter table command).
> Could anyone throw some light on altering the size of columns on a
> table with 400000+records without any problems like the one above?
> TIA for any help.
> Regards
> Pradeep.L
Pradeep.L,
If you right click on your database in EM and select "Properties" and
"Data File tab" what do you see? Is the "restrict file growth" radio
button checked? Are you trying to make the db bigger than is allowed?
Or are you running out of disc space on the drive where this db/file
group is stored?
Kristina
|||Hello Kristina,
[vbcol=seagreen]
No. It is set to "Unrestricted File Growth" for Datafile and
Transaction log.
[vbcol=seagreen]
I'm not sure about this. The maximum db size that is permitted in our
machine is 2GB and it complains that the cumulative size should not
exceed 2048 MB due to the license policy.
[vbcol=seagreen]
group is stored?
There is 15GB free space in the partition in which sql server is
installed -
BTW, The purpose of trying out this issue is we need to increase the
size of quite a few columns in our production server.
Most of the columns are part of composite primary key/Index. We would
like to ensure that we don't land in sticky waters
when we go out with the change in our production server -
|||<pradeepln@.hotmail.com> wrote in message
news:1178942162.311166.101130@.e65g2000hsc.googlegr oups.com...
> Hello Kristina,
> No. It is set to "Unrestricted File Growth" for Datafile and
> Transaction log.
> I'm not sure about this. The maximum db size that is permitted in our
> machine is 2GB and it complains that the cumulative size should not
> exceed 2048 MB due to the license policy.
Ummm, 2048 MB IS 2GB.
Sounds like you've reached your max. Or I'm misunderstanding you.

> group is stored?
> There is 15GB free space in the partition in which sql server is
> installed -
> BTW, The purpose of trying out this issue is we need to increase the
> size of quite a few columns in our production server.
> Most of the columns are part of composite primary key/Index. We would
> like to ensure that we don't land in sticky waters
> when we go out with the change in our production server -
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html