SQL Server Performance

Error DEFAULT FILE GROUP IS FULL

Discussion in 'General DBA Questions' started by ksmurthys, Dec 3, 2004.

  1. ksmurthys New Member

    HI ALL,
    WHEN I am querying data using sql statement
    SELECT Col1,COL2,COl3..
    UNION
    SELECT Cil1,col2,col3..
    UNIon
    ...
    ...
    I am getting the following error
    "Could not allocate space for object '(SYSTEM table id: -167114239)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full"
    How to resolve this issue?

    Regds,
    Sriram.
  2. mmarovic Active Member

    You don't have enough space on a drive tempdb resides or you limited tempDb size and you don't have enough space there to perform union. Your query obviously uses tempDB and there is not enough space there.
  3. ksmurthys New Member

    Hi
    IS THERE ANYWAY I CAN RESTRICT THAT QUERY TO RUN ON MY PRIMARY FILE GROUP?


    Thanks,
    SRiram.
  4. mmarovic Active Member

    Any query will use all filegroups available for tempdb. The problem is that you don't have enough space for this query (and others that were running at the same time) in tempDB as a whole.
  5. thomas New Member

    Make sure you have moved tempdb's data file to a drive with lots of space (typically, either its own partition, or the same partition as all your user databases' data). Use ALTER DATABASE tempdb ALTER FILE.. etc (see BOL for details.) Maybe your tempdb is still in your default data location along with master, msdb, model, pubs & northwind and needs to be moved? Sometimes this is C:program files.. etc.. or somewhere else unsuitable.

    Don't forget to move its log too, if necessary (and it usually is).

    Tom Pullen
    DBA, Oxfam GB
  6. mmarovic Active Member

    and please give us some feedback this time

Share This Page