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.
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 Crogram 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
