November 1, 2002 at 12:50 am
Hi,
One of my users got an error that read :
"Could not allocate space for object <tablename> in database <databasename> because the 'PRIMARY' filegroup is full."
What follows are a list of steps followed(and the settings on EM - we use SQL Server 7.0)
i) Freed up about 2GB of space on the disk(since I assumed that the problem occured due to lack of free space)
ii)Went to EM and right clicked on the DB and in the Properties option chose the 'Options' tab and checked 'Truncate Log on checkpoint'...(other than this - only 'Auto create statistics' and 'Auto update statistics' are checked)
iii)Settings for the DB in EM properties option for the MDF and LDF files are :
File Properties :
Automatically grow file (selected)
File growth - By percent(10%)
Maximum File size - Unrestricted filegrowth (selected)
This problem still tends to recur even though there is enough free space on the disk...am I missing something out here??or do I have to make any changes in the settings?
Regards
winash
November 1, 2002 at 3:59 am
Is this for the creation of a table or maintainence of a table? If you are doing for instance DBCC REINDEX the table will grow to physically twice it size while doing this in many cases. If there is not enough space on the HD to allow for this you will then get this error. Again what is the customer doing when it happens and look for related issues there.
November 1, 2002 at 2:43 pm
Excuse this comment but are you sure you have space on the disk that the database is on. I know people assume it is the database they are working in but often is tempdb.
I am alos sure that a file can only grow a certain amount of times, therefore if the database started at 1Mb and has grown by 10% then it might have reached this limit. Thing is I can;t find the limit anywhere so might be getting confused with Oracle
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 1, 2002 at 3:27 pm
Simon, the growing a set number of time only applies if you set a restricted growth amount. Most folks leave as unrestricted growth, and that is generally because they didn't look or didn't care. I didn't think Oracle was restricted either, but now I am curious and will hav to check on.
Good point about tempdb, I did run into someone awhile back who setup tempdb like a really good dba on a seperate drive from the databases and forgot which he was looking at.
November 2, 2002 at 5:50 am
Only used Oracle at version 8 and you definitely had to be careful becaue you could only grow a file a fixed number of times, and thought a similiar thing applied to SQL Server. i.e a built in server restriction of limiting the number of growths to 2000, irrespective of the maximum size that is set.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 2, 2002 at 9:05 am
Interesting, I will have to look into this is one of our test environments if I can get time.
November 5, 2002 at 5:38 am
I got the same error yesterday.
Error: 1105, Severity: 17, State: 2
Could not allocate space for object 'tablename' in database 'database' because the 'PRIMARY' filegroup is full.
The amount of free space was 3.5GB on the log disk and 34GB on the data disk. The data file was 7GB and the log file was 1.5GB.
So there seemed to be no problems there.
I tried to expand the data file but with no luck, I got no error messages with the alter database command but when I checked the size it was the same as before.
I then moved the data file to another disk with 58GB free space with detach and attach and that solved the problem, no more error messages.
But I still can not understand why I got the error. Can anyone explain this?
November 5, 2002 at 6:46 pm
No as something had to be preventing it. Maybe there is something to what Simon stated but how many times have you grown the file? I have several that are well on there way to 2000 times grow and shrink. Did you check the Event Viewer logs to see if anything unusal there.
November 6, 2002 at 1:02 am
Hi,
I came across the following in the Microsoft Knowledge base articles...
If you delete text or image data, space may not be reused when new data is inserted. This can lead to the sp_spaceused stored procedure showing unused space within a table, but an attempt to insert new data will either cause the relevant files to grow or fail with errors of type 1105:
Could not allocate space for object '%.*ls' in database '%.*ls' because the '%.*ls' filegroup is full.
WORKAROUND
To defragment the text or image data and recover the unavailable unused space, use the bulk copy program (BCP) to bulk copy the data out of the table and then back into the table so that the storage will then be contiguous.
November 6, 2002 at 4:17 am
Then only yhing with that is you should have failed even after the move. As well, the I am pretty sure that is the article I read awhile back where this was referring to text columns being set to live in their own filegroup. And third you had plenty of room to grow with no max limit set for growth. Could still be related but not is the issue at hand here.
One question I didn't ask, do you have the database set to auto shrink? If so then you could have potentially took the whole drive up, ran out of room, rollback left a lot of free space and auto shrink awoke and removed the free space. Just a thought.
November 7, 2002 at 12:32 am
In my case I didnt have the database set to auto shrink.
I don´t think that the database has grown so many times, not 2000 times anyway. When I created the database the data file was 5Gb and now it was 7Gb and autogrow was set to 10% at a time.
November 7, 2002 at 4:34 am
Another thought, do you copy any files down that get deleted during processing? Also, have you run the checkdisk utility (can be found by right clicking the drive, choose properties then tools tab, listed as "Error-Checking", might have garbage on the drive that is blocking it or bad sectors that you are unaware of.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply