SQL Server Performance Forum – Threads Archive

Error DEFAULT FILE GROUP IS FULL

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.

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.

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

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.

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

rogram 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

and please give us some feedback this time

]]>