Could not allocate space for object '...' in database 'TEMPDB'

25 Aug 2006 8:55 PM

nkw

select count(*)
from t1
inner join t2 on t1.c_id = t2.c_id
inner join t3
      on t3.U_ID = t1.u_id
         and t3.L_ID = t1.L_ID
         and t3.D_ID = t1.D_ID
         and t3.P_ID = t1.P_ID
inner join t4 on t4.cr = t3.cr_id

t1 and t3 are big table with 10M and 30M rows. My tempDB has 10G space
limitation. However, I still got the following error message.

Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object '(SYSTEM table id: -1027564065)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.

Any suggestion?

25 Aug 2006 9:00 PM

Aaron Bertrand [SQL Server MVP]

http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html

Show quote

"nkw" <n**@discussions.microsoft.com> wrote in message
news:9DA327BB-F313-420F-B885-07E36FC451B3@microsoft.com...
> select count(*)
> from t1
> inner join t2 on t1.c_id = t2.c_id
> inner join t3
>      on t3.U_ID = t1.u_id
>         and t3.L_ID = t1.L_ID
>         and t3.D_ID = t1.D_ID
>         and t3.P_ID = t1.P_ID
> inner join t4 on t4.cr = t3.cr_id
>
> t1 and t3 are big table with 10M and 30M rows. My tempDB has 10G space
> limitation. However, I still got the following error message.
>
> Msg 1105, Level 17, State 2, Line 1
> Could not allocate space for object '(SYSTEM table id: -1027564065)' in
> database 'TEMPDB' because the 'DEFAULT' filegroup is full.
>
> Any suggestion?

25 Aug 2006 9:46 PM

nkw

EXEC sp_spaceused display strange message:

database_name    database_size    unallocated space
tempdb    53343.63 MB    50624.63 MB

And SELECT OBJECT_NAME(id), rowcnt
    FROM tempdb..sysindexes
    WHERE OBJECT_NAME(id) LIKE '#%'
    ORDER BY rowcnt DESC

returns nothing.

25 Aug 2006 9:50 PM

nkw

And both database_size and unallocated space are keeping growing.

database_name database_size unallocated space
tempdb 58406.19 MB 55687.20 MB