SQL Server Performance

Could not allocate space for object

Discussion in 'General DBA Questions' started by surendrakalekar, May 26, 2005.

  1. surendrakalekar New Member

    "Could not allocate space for object 'COMPANY_EXPERIAN' in database 'JUNE' because the 'PRIMARY' filegroup is full."
    This database is on the remote server. How to fix this problem?
    surendrakalekar, May 26, 2005
    #2

  2. gkrishn New Member

    1) Check 'autogrowth' is enabled for datafiles
    If so
    Please check if your drive has enough space for the datafiles to grow



    rajiv
    gkrishn, May 26, 2005
    #2

  3. dineshasanka Moderator

    indicates that your database CDS does not have enough space left to continue
    the Insert.

    Expand the database through Enteprise Manager.


    quote:Originally posted by surendrakalekar

    "Could not allocate space for object 'COMPANY_EXPERIAN' in database 'JUNE' because the 'PRIMARY' filegroup is full."
    This database is on the remote server. How to fix this problem?
    dineshasanka, May 26, 2005
    #2

  4. surendrakalekar New Member

    Thanks
    Autogrowth options is checked (10 %). But Auto Shrink option is not checked. Is it becuase of that?
    Also the object object 'COMPANY_EXPERIAN' table contains so many field with varchar(8000).
    Dinesh what is CDS?
    surendrakalekar, May 26, 2005
    #2

  5. gkrishn New Member

    No that is not an issue.You sould not check autoshrink option for Prodution databases.

    I believe your drive is full. Use xp_fixeddrives to check free spaces available in the drive .
    gkrishn, May 26, 2005
    #2

  6. dineshasanka Moderator

    Yes donot check auto shrink option<br />I mean hard dirves <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by surendrakalekar</i><br /><br />Thanks<br />Autogrowth options is checked (10 %). But Auto Shrink option is not checked. Is it becuase of that?<br />Also the object object 'COMPANY_EXPERIAN' table contains so many field with varchar(8000).<br />Dinesh what is CDS?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
    dineshasanka, May 26, 2005
    #2

  7. surendrakalekar New Member

    Gkrishn,
    I checked with xp_fixeddrives. There is enough space (Around 17GB).
    surendrakalekar, May 26, 2005
    #2

  8. satya Moderator

    Be aware that the system tables ALWAYS live on the PRIMARY file (which is also the DEFAULT PRIMARY filegroup by default).

    So if your PRIMARY file is full, SQL Server cannot add another entry to the SYSFILES system table, etc when you want to add another SECONDARY file to that database.

    Otherwise, Auto-Gowth does not guarantee that your files will not run out of space (disk drive space permitting as with certain operations, such as creation of your CLUSTERED INDEX, reindexing, etc, the way the operation is done internally (via the log, allocation of extents/pages, etc) means effectively that you need the space before the operation runs.

    As an aside, if you do have Auto-Growth turned on, it is best to use a static increment as opposed to a percentage.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, May 26, 2005
    #2

  9. gkrishn New Member

    I beleieve your autogrow function cannot "keep up".YOu can add one more file and set its autogrowht to 10% again.

    I am not sure whether DBCC shrinkfile will help in this situation.

    rajiv
    gkrishn, May 26, 2005
    #2

  10. surendrakalekar New Member

    Thanks everybody,
    This error occures due to batch update. Autoshrink option is not checked. We keep the option autogrow in percentage and change the unrestricted file growth with restrict file growth, and given allocate enough space for it. There were 2-3 million rows for updation. This will take hours to update. Hopefully there will be no error this time. I will let you know the result.
    surendrakalekar, May 26, 2005
    #2

  11. surendrakalekar New Member

    Thanks once again.
    The problem is solved. But want to understand such problem in depth to avoid it in future. Provide me link if you know any.
    surendrakalekar, May 26, 2005
    #2

  12. satya Moderator

    satya, May 27, 2005
    #2

  13. CGSJohnson New Member

    You said that the problem was solved. How did you solve it? Thanks.

    Chris

    - Chris
    CGSJohnson, Dec 19, 2006
    #2

  14. satya Moderator

    Chris
    Can you explain your problem more in detail and also refer to the Books online as referered.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
    satya, Dec 19, 2006
    #2

Share This Page