Could not allocate space for object, 'PRIMARY' filegroup is full

[romainp]

Hi,
I hope that someome could help me with this issue and give me some hints on how resolve it.
The issue is with the job optimization. first, let me give you some settings :

I have an application that have a db in SQLServer 2000. The application have created a mainteance plan that does the folowing :
- Optimization :
* Reoganize data and indexes pages
** change free space per page percentage to : 10%
* Remove unused space from database file
** Shrink database when it grows beyond : 50 MB
** Amount of free space to remain after shrink : 10%

For information, xp_fixeddrives gives me :
C : 5331
D : 174932 -> all dbs files are located here
E : 3817
S : 33044


The db itself have the folowing size, according to the taskpad view :
* Space allocated
** Data : 110785 MB (106753 used, 4032 free)
** Log : 175.99 MB (14.19 used, 161.8 free)

Also, the data and log system files are on the D: drive

For the data file settings :
* Space allocated for data : 110787 MB (PRIMARY)
* Auto grow file is set, file growth in MB : 256
* Max file size, restrict file grow : 110880
For the Log file settings :
* Space allocated for data : 176 MB (PRIMARY)
* Auto grow file is set, file growth in MB : 128
* Max file size, restrict file grow : 22176


The tempdb now,
* Space allocated
** Data : 8 MB (1.12 used, 6.88 free)
** Log : 0.74 MB (0.34 used, 0.4 free)

Also, the data and log system files are on the D: drive

For the data file settings :
* Space allocated for data : 8 MB (PRIMARY)
* Auto grow file is set, by percent : 10
* unrestricted file growth
For the Log file settings :
* Space allocated for data : 1 MB (PRIMARY)
* Auto grow file is set, by percent : 10
* unrestricted file growth

Now, for the issue itself, everytime the optimization job is launch, the error log gives me :
Could not allocate space for object '(SYSTEM table id: -637536758)' in database 'DB' because the 'PRIMARY' filegroup is full..

I have cheked on the internet without finding the right way to solve this issue and I am not an expert in SQLServer 2000.

I suspect that when the job is launch, the reindexing or the remove spave operation is filling the DB space to the max but I not sure of this...
What setting should I need to modify in order to the job to complete without failure? I am pretty sure that some settings ar e not correct or that I need to add more space to the DB, but what and where is the question...
The only drive that I can use is the D drive whitch has as you can see plenty of free space.

Please, give me any hints to tricks that could help me (and others).
If you need more details, feel free to ask.
Thanks.

 

[mrdenny]

The problem is because your database is restricted from growing any further. You need to remove the growth restriction from your database and rerun the job.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[romainp]

Thanks mrdenny,
I will test your solution but still I have a quick question your you : if I remove the growth restriction, will the maintenance plan (optimization) work during the database growth? Should I expect a first 'failure' because the db needs first to be upsized enough so the optimization will have enough space to work?

 

[mrdenny]

Yes the Maintenance plan will work while the database grows. Database growth is a normal function of the database engine and is performed online. The maintenaince command is suspended while the file is grown, the the command continues.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[romainp]

Hi,
I have applied the settings that you told me to set in order to solve my optimization job issue.
- For the database, I have set the transaction log to 'unrestricted growth'
- I have removed the option 'Removed unused space from database files' and only have 'Reorganize data and index pages/Change free space per page percentage to : 10%.

But the optimization job still failing...
The log gives me the info :
------------
Rebuilding indexes for table 'ACL'
...
Rebuilding indexes for table 'SensorData1'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table id: -607234322)' in database 'RealSecureDB' because the 'PRIMARY' filegroup is full.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
-----------

According to the taskpad view, this table have the following size :
dbo.SensorData1 : rows = 73708159; size = 25802392 KB, Index size = 4932152

Can you help me to solve this issue?
The log says that the primary filegroup is full.. ok but how can I add more free space and where? Should I create another filegroup and if yes, how?

Thanks for all good advices that you could give me.

 

[mrdenny]

You also need to set the data file to unrestricted growth.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[romainp]

Thanks mrdenny,
Unfortunatly I can set it to unrestricted growt ... because of the backup.

In fact even if I an plenty of free space the way we do our backup is to use the maintenance plan backup option and put the file in a different drive. And this drive as limited size (the backup file almost fill ip up)... unless there is a way to compress the resulting backup file I can't do much for this part.

But what I want to know is : if I grow the size of my db (the data file size in fact) does the optimization will work?
Thanks

 

[mrdenny]

If you give the database enough space yes the optimization will work.

Having empty space in the database file will not increase the size of the backup.

If the backup file is filling the drive, you should look into getting larger disks for that drive. Storage is cheep, especially when compared to not being able to backup your database.


Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[romainp]

Thanks mrdenny and I am totally agreed with you concerning the backup.
I know that it depends on a lot of factors but In your opinion, how much should I growth the database data file? I have read somewhere that with a calculation of the biggest table size could give me a hint on how should I growth my database so the optimization will work, but don't really know how to make this calculation ....
Thanks

 

[mrdenny]

It doesn't have much to do with the size of the objects.

It's got to do with the amount of growth of the database per day, along with the amount of time it takes to grow the file. You want the growth size to be large enough that it doesn't grow that often, but small enough that commands do not time out while the database is growing.

I usually use 256-1024 Megs at a time for the auto grow.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[romainp]

Thanks,
And of course, I have to unset the setting 'Max file size, restrict file grow : 110880' on the db or set it to a higher value?

 

[mrdenny]

I leave it unset. Set the database grow to the size it needs. If you lock it down, and it needs to grow to do something the command will fail.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com