SQL Server Performance

default filegroup is full

Discussion in 'Performance Tuning for DBAs' started by tariq745, May 24, 2007.

  1. tariq745 Member

    Hi Guys<br />I am getting the flowing error when I am trying to run a job which moves data. I am running sql server 2000, sp 3a, I have 4 files in temp db 50mb each (no auto grow) all 4 files have 47mb free space left. Temp db (current) size is 8192kb and log file size is 512kb. Any idea why I am getting this error? Thanks in advance.<br />Error =<br />Executed as user: sa could not allocate space for object ‘(system table id – 392323383)#%92 in database ‘tempdb#%92 because the default filegroup is full [sqlstate 42000] [error 1105] unable to open step output file. The step failed.<br /><br />Tariq[<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br />
  2. satya Moderator

    Why you have disabled AUTOGROW in TEMPDB?
    Have you tested by enabling the AUTOGROW on TEMPDB?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. tariq745 Member

    It was a recommendation by microsoft in our meeting. That Y we disable the auto grow and made 4 files
  4. Luis Martin Moderator

    Also the recommendation was to split tempdb en 4 files?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  5. tariq745 Member

  6. Luis Martin Moderator

    Some times could be necessary to split tempdb, but I don't agree with autogrow off.
    If I were you I will set autogrow on but in MB, and set some value.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  7. tariq745 Member

    Even if I enable autogrow I dont think that is the main cause of the problem.
  8. Luis Martin Moderator

    Could be not.

    Now, if you use to stop and restart SQL service from time to time, then tempdb will be cleaned and almost empty.
    If that is not the case, I think you will have problems with autogrow off.




    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  9. tariq745 Member

    but my log file size is only 512kb
  10. Luis Martin Moderator

    And log is also with autogrow off?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  11. tariq745 Member

    no log is on on auto grow (unrestrict grow)
  12. Luis Martin Moderator

    Well, if you don't stop and restart sql to clean tempdb I don't know what more suggest you.

    Wait for others members opinions.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  13. bvnashok New Member

    It looks like you database job needs more space than available free space and it is unable to allocate more space, because autogrow option is disabled.

    Is it 47 MB total free space or in each data file?

    You can try two ways:
    Shrink the data files in TempDB, then you can get little bit more space.
    Add one more data file to the file group, if you have disk space.

    Do you remember any specific reason, microsoft recommends to disable the autogrowth?

    I can expect one reason that, you will easily trackly the problems like this.

    Let me know
  14. tariq745 Member

    well its a long story why MS suggested us to make 4 files. I will post that later may be tomorrow since I have to catch a flight BUT I will post the reason tonight.
  15. Tali_SQL New Member

    Sorry for the delay. stop and restart service solved the problem

Share This Page