What should be in Temp DB for Temp tables?

Question

What should be in Temp DB for Temp tables?

TangoVictor

SSChampion

Points: 10948
More actions
October 17, 2011 at 9:28 am

#258878

The temp db was around 4GB with nearly 4GB free.
was able to free space with a file shrink.
after running the following query I see there are temp tables in temp db. the total page count is around 100.
Is this normal? Should these tables clear themselves? Should temp db be giving up disk space or should there be a scheduled shrink?
SELECT Name=so.name, TotalPages=total_pages
FROM tempdb.sys.objects so (NOLOCK)
JOIN tempdb.sys.partitions pa (nolock) ON so.object_id = pa.object_id
LEFT JOIN tempdb.sys.allocation_units al (NOLOCK)
ON (al.type in (1, 3) AND pa.hobt_id = al.container_id)
OR (al.type = 2 AND pa.partition_id = al.container_id)
WHERE name LIKE '#%'
ORDER BY Name
There are no open transactions. Ran the following to check. DBCC OPENTRAN
Was referencing the following article. We are running 64bit standard sp3
http://support.microsoft.com/kb/931843/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

perfectly normal;

the SQL server may create temp tables all on it's own to handle things like sorting operations for a dataset to be returned; they don't have to be explicitly created via a declare #temp process.

the tables will eventually be dropped, but the space will not be given back; the assumption is if you needed the space once, you may need it again, so the space is maintained until the service gets stopped and restarted;

when you were at 4 gig, hopefully it was a one time process like an ETL that made it grow large like that; if you find it at 4 gig again, you might want to make it default to that size to prevent it from having to grow.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David H. SSC Enthusiast Points: 187 More actions · Answer 2

Exactly. Tempdb is also SQL Server's scratch pad for various work it needs to do in aggregations, joins and other work. There will always, always be things laying around in tempdb.

As for sizing and shrinking. Shrinking it all the time isn't the best of practices (but I know of folks that do it). Baselining its use relative to your user database and application usage is the only way to evaluate how you should size it, and then monitor it to understand if its current size is "good" or "bad".

TangoVictor SSChampion Points: 10948 More actions · Answer 3

Thanks for the replies. since we've implemented more file size monitoring we are now more aware of changes. Like you said probably best to base line it before making any changes. if 4gb is where it likes to be then it's best to just leave it there.

thanks