TempDB filled disc ... Have error message ... have a couple of questions

Question

Morning ALL,

OK ... had TempDB fill the disc last Saturday morning.

Error generated was:

Could not allocate space for object 'dbo.SORT temporary run storage: 167566660009984' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

I have dealt with TempDB's filling up more times than I care to mention so nothing new here.

I do however have a question:

1. What does the number 167566660009984 mean in the error message listed above?

Thanks

Answer 1

• what is current size of tempdb (log & data file )
• add new file to tempdb for having more space
• check what all objects are occupying space in tempdb

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 2

simple space is not enough to perform this operation incase if it is performing your operation only during this case u need to add additional file in it and if it is any other activity running like bulk operation then u have to think in differnt way that is schedule anothe schedule ..so iam guessing the tempdb is not enough operation and might be restricted so think about that and do the needful that will resolve the operation very simple..

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

Answer 3

I believe the number is the objectid of the object needing space.

Answer 4

Thanks Tom,

You seem to be the only one really reading my post ...

For the others, I was not interested in how to manage TempDB as I am VERY fluent in that - lol

All I was asking is what the number represented

I beleive the temp table that the objectid referenced is no longer there since the offending SPID that used that object (and filled TempDB) rolled back and removed the temp table.

Was trying to see if the table did exist if I could look at its contents and see what may have been the process that spawned it.

Thanks Again!

Answer 5

It is a rowset id used to identify sort table internally.  When a query with sort operator (most likely from "order by") does not have enough memory, it writes to tempdb to store partially sorted runs for later sort merge.  This should not be visible outside of the current query context.  This rowset id has no practical value for end user.  Thanks.

Answer 6

Thanks Jay