'PRIMARY' Filegroup is full - error

  • winash

    SSChampion

    Points: 11446

    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

  • Antares686

    SSC Guru

    Points: 125444

    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.

  • Simon Sabin

    SSCrazy Eights

    Points: 8142

    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

  • Antares686

    SSC Guru

    Points: 125444

    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.

  • Simon Sabin

    SSCrazy Eights

    Points: 8142

    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

  • Antares686

    SSC Guru

    Points: 125444

    Interesting, I will have to look into this is one of our test environments if I can get time.

  • lehtonenj

    SSC Enthusiast

    Points: 161

    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?

  • Antares686

    SSC Guru

    Points: 125444

    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.

  • winash

    SSChampion

    Points: 11446

    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.

  • Antares686

    SSC Guru

    Points: 125444

    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.

  • lehtonenj

    SSC Enthusiast

    Points: 161

    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.

  • Antares686

    SSC Guru

    Points: 125444

    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 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply