Could not allocate space for object

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Could not allocate space for object

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-06-16 : 09:29:46

I am trying run a query and I am getting the following error message:

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

Anyone know how to correct the problem.

Thanks

mr_mist
Grunnio

1870 Posts

Posted - 2004-06-16 : 09:38:53

Make more space for tempdb? ;)

What's running? If it's a select, check for mis-written joins. If it's an update/insert, try increasing your disk space or reducing the size of the batches. Look at your logging configuration.

-------
Moo. :)

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-06-16 : 09:48:42

Thanks. I was looking this up on the Microsoft support site and found the following workaround. I am not sure how to impliment it.FYI, I am not a DBA, but the only thing this Dept has to having one. SO excuse me, if I don't understand completely all the terminology or concepts.

Workaround:
To defragment the text or image data and recover the unavailable unused space, use the bulk copy program (BCP) to bulk copy the data out of the table and then back into the table so that the storage will then be contiguous.

Thanks

mr_mist
Grunnio

1870 Posts

Posted - 2004-06-16 : 09:57:41

That's an obscure workaround that may not be applicable. You'll need to provide more specific information about what is happening to cause this error.

How much disk space do you have?

-------
Moo. :)

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-06-16 : 10:01:53

I have a bout 20Gb of diskspace on that server.

The link for the workaround is:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;272220

mr_mist
Grunnio

1870 Posts

Posted - 2004-06-16 : 10:08:55

Do you have image data within your database? Have you recently deleted some? If not, that's not the error / fix you are looking for. The fix is basically suggesting that you export your database and then reimport it. Lots of effort.

Again, what is it that you're actually doing that causes the error?

-------
Moo. :)

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-06-16 : 10:13:49

I am trying to do an insert, but even if I try writing a select statement, I get the same error. The data I am trying to inser/seletct is text not images. The statement I am trying to execute is:

Select * from workApp.dbo.osrinit
group by Unit_coll, Occ_num, Det_coll, Division,Line, Extracted,Status, Year_open, Month_open, Year_last, Month_last, Ass_coll, Jur_coll, Supp_unit, Zone, A_osr_code, Detail_typ, Stats_can, Reported, Unfounded, Outcome, Clear_chg, Clear_oth, Adult_M, Adult_F, Youth_M, Youth_F, Yth_no_ch, Comp_chg, E_Estimate, E_Recovery, E_Fines, F_Osr_code, F_Year, F_Month, F_Day, Class, Feild13, Country, Work_del, Property, Quantity, Proceeds, Field19, Field20, Estimated, Recovery, Revenue, Field24, Referral, Restitutio, Fines, Charges_M, Charges_F, Charges_C, Jail, Probation, Community, Detachment, [Key]
having count(occ_num)!=1
order by unit_coll,occ_num, line

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

Thanks

dbthj
Posting Yak Master

143 Posts

Posted - 2007-11-13 : 12:47:32

I am having a similar problem. An application process does a LOT of inserting and gets the same message. But there is 15 gig free on the disk (at least, when I look at it). I am not convinced that the answer is one of the obvious ones. So my two questions are these:

What is a "SYSTEM table id" (from the error message)? Where do I
look it up?

I have looked at sysobjects in the user database and in master and the "table id" reported does not exist as an object id. There are also system table id's in syscacheobjects, but ... same deal. The reported object id is not there. In 4 failure events, 4 different "SYSTEM table ids" were reported, none of which seem to exist. I'd like to know just what this thing is that is not getting space.

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-13 : 13:40:01

In order to GROUP BY
SQL server has to sort the table osrinit
It is done in a temdb and there is not enough space there

How big is a table?
What are you trying to do? Get rid of the duplicates?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-13 : 23:04:52

Did you let db files auto grow?

dbthj
Posting Yak Master

143 Posts

Posted - 2007-11-14 : 10:36:40

All database files are autogrow, unlimited space, including tempdb.
Tempdb has the data on drive E: (60+GB free) and logs on D: (15GB free)

I don't know why tempdb would need logs, but that's not important now. I still would like to find out what object (exactly which
"system table") is having the space problem. The object id's
reported in the error messages do not appear in sysobjects or
syscacheobjects. Does anybody know where to look for this information?

Some forum threads suggest that the DBMS can get tired of waiting
for an extent and crap out. With Automatic Grow and 10% growth
that doesn't seem likely.

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-14 : 13:05:59

You will not find that object by the id.

When you do SELECT ... GROUP BY A,B,C
and there is no clustered index on A,B,C

SQL server has to:
sort the table in order of A,B,C
save the result into tempdb (in your case - all the table as you put almost all columns in SELECT list)
traverse that temporary object again. Calculate cumulative totals each time there is a new 'chapter' of A,B,C (this operation is called 'Sorted Stream Aggregation'

So the object Id you find in the message is an Id of a temporary internal object, required for sorting.

dbthj
Posting Yak Master

143 Posts

Posted - 2007-11-14 : 15:25:47

I haven't actually mentioned any specific queries, or an
order-by or a group-by. Actually, I do not know what the SQL is. But your explanation of the temporary objects in tempdb makes sense. However, there is 60 GB free on the drive with the tempdb data. That's a lot of sort space.

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-14 : 16:00:58

>I haven't actually mentioned any specific queries

I was replying to the original post of mdhingra01
He/she provided the exact query.

dbthj, are you working in the same company?
I am a little bit confused...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-14 : 16:12:29

evilDBA, dbthj hijacked a 3-year-old thread. I'm sure that the OP worked out this problem already.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-14 : 16:14:05

2004... WOW :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-14 : 16:21:42

quote:
Originally posted by dbthj

I haven't actually mentioned any specific queries, or an
order-by or a group-by. Actually, I do not know what the SQL is. But your explanation of the temporary objects in tempdb makes sense. However, there is 60 GB free on the drive with the tempdb data. That's a lot of sort space.

Manually grow the tempdb MDF file so that SQL Server doesn't have to spend time to do it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

dbthj
Posting Yak Master

143 Posts

Posted - 2007-11-15 : 09:16:29

Manually growing temp space sounds to like asking for trouble.
Back in my former life as a mainframe/DB2 guy this was the way
to do it, but then I had one or two instances to watch,
not a couple dozen.
I don't want to hog a lot of disk space that won't be used. I
don't want to get paged a lot for application failure. Is there
a way to track the highwater mark for tempdb size? This would give
me some hints for sizing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources