SQL Server Performance

DEFAULT filegroup is full

Discussion in 'General DBA Questions' started by sonnysingh, Jun 29, 2007.

  1. sonnysingh Member

    Hi Folks


    Error: 1105, Severity: 17, State: 2
    Could not allocate space for object '(SYSTEM table id: -669903346)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.

    I have received this error message when running a Store procedure.. what should I do and how?


    Thanks in Advance
    sonny

  2. satya Moderator

    Ensure you let tempdb grows automatically and there is enough disk space to host it.
    Change tempdb's initial size to make it bigger. And you can expand tempdb manually before run your process.

    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. MohammedU New Member

    What is your tempdb size?
    Follow the Saty's recomendations...

    I think your procedure is using temp table and/or procedure doing some kind of sort operations and joins causing the optimizer to create temp table...

    So check your procedure too...what is cuasing to tempdb grow in your procedure code...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

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


    TempDB sizes follow:-

    Data File: 407 MB
    Log file: 27 MB
    Autogrow on with 10 percent unrestricted growth
    recovery mode: simple

    ON GEneral Tab (properties)
    Size: 432.81 MB
    Space available: 421.52 MB

    of course Mohammed SP using temp table...and that's why this happenig..but I was faced this before as I never involved this kind of situation where I need to expand or transfer tempdb to the another disk (or partition). Please refer any article or thread that give us how to expand (or transfer tempdb another disk) tempdb.

    Thanks guys...
  5. satya Moderator

  6. MohammedU New Member

    quote:Originally posted by sonnysingh


    TempDB sizes follow:-

    Data File: 407 MB
    Log file: 27 MB
    Autogrow on with 10 percent unrestricted growth
    recovery mode: simple

    ON GEneral Tab (properties)
    Size: 432.81 MB
    Space available: 421.52 MB

    of course Mohammed SP using temp table...and that's why this happenig..but I was faced this before as I never involved this kind of situation where I need to expand or transfer tempdb to the another disk (or partition). Please refer any article or thread that give us how to expand (or transfer tempdb another disk) tempdb.

    Thanks guys...


    Check articles provided by Satya...
    Don't expect sql behaves all the times same way....
    It is sql optimizer who decides how to handle the query...and also depend on the time and size of the data....
    When you are executing your procedure there might be another activity on the server which might consumed tempdb a lot...


    MohammedU.
    Microsoft SQL Server MVP
    Moderator
    SQL-Server-Performance.com

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

    I will try to expand first and see how it is going to react in future. If any trouble again then move to another disk...

    Thanks a lot guys...

Share This Page