We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


why sql server keep growing tempdb while there's still plenty of free space in it?

thomasjintao asked
Medium Priority
Last Modified: 2012-08-13
The tempdb in our production (SQL Server 2005) environment keeps growing (reached 200GB last week), but the most of the space (99%) is free. "sys.dm_db_file_space_usage" shows that most page allocation happened in internal_object_reserved_page_count. Why will SQL server grow the tempdb while there's plenty of free space in it ?
We have a farm of web servers connecting to the db server with a fixed service account. Connection pooling is used by ADO.NET.
We don't mind the SQL server use the tempdb, the real problem is that the server keep growing the tempdb without considering how much free space in it.
Watch Question

I dont know why its happening cause I am not too aware of 2005's structure, but you can go to DB properties and uncheck automatic growth, and give it a set Max size


Set a max size will cause query to fail if that max size is reached at some peak time, which we don't want it to happen. What I want to know is what can cause the SQL server to grow the tempdb while it seems that there's still plenty of free space in there.

You don't want to set a max size if you can help it otherwise all DB processing will stop when that size is reached.
SQL recreates the tempdb log each time sql is stopped/started. If this does not shrink the log size back to a reasonable size (or if restarting SQL is not an option) then you can shrink the log file by going into the properties of tempdb and where the original log file size is shown... setting it to a new value. Note that this value must be larger than the current (used) size of the log.

The log file growth would have happened when the log was full, consider setting up an alert that emails you and/or writes to the application log each time tempdb_log needs to grow. Maybe you can use this to figure out what is filling up the log.

what is ur default size fro ur tempdb. u need to use a alter statement to change ur tempdb size to be small and then u need to restarted sql server.

also whenever u restart sql server ur tempdb re-inializes back to 2 mbs.


There's no problem with log file, it's the tempdb data file that's growing crazy.  
The default tempdb size is 8GB.
Restart SQL server makes it rebuild the tempdb. But it still starts to grow after a while.

dbcc shrinkfile(tempdev) changing tempdev to suit
Monitor as suggested above and try to establish what activity (bulk procesing/extensive use of temporary tables) is causing this problem.


shrink db will freeze the server, and we don't want to do it constantly in the production environment.
We know we have expensive queries that will use the tempdb (LOB, trigger, cursor, ...), that's not a problem for us. The real problem is: shouldn't the space be freed after the transaction? (according to msdn documentation, Internal objects are created and dropped within the scope of a statement).

One thing I should mention is that:
In sys.dm_db_file_space_usage, the number in unallocated_extent_page_count is low, and internal_object_reserved_page_count is very high (but this should include the unused pages). sp_spaceused shows that unallocated space is almost 99% of the database size. This means most of the free space is in internal_object_reserved_page_count. Seems that the SQL server only check the  unallocated_extent_page_count before growing the tempdb?


To my knowledge, the space should be "freed up" within the DB but the space not given back to the OS until such time that you restart SQL or run a shrinkfile command. Don't take that as gospel though as I've never really had a problem with tempdb sizes.


Sure these freed pages are within the DB file, the question is: when the sql server need to allocate page for use, why will it expand the db file instead of using the existing free pages?  

I'm not convinced there are any free pages when it grows. There maybe after a certain period when temp objects are dropped but during heavy processing it sounds like it is having to grow to accommodate this activity.


Then why it's growing every day?


Unless they are not freed because of the connection pooling. And sp_spaceused is cheating.

If (example sizes) your DB is 10mb and it fills then it grows to 15mb.
Then when objects are dropped the contents of the DB drops to 5mb but the allocated size for the DB remains at 15mb.

Day 2
Your 15mb DB grows to 25MB to handle some heavy processing. Objects are dropped and the contents drop to 2mb, the allocated size remains as 25mb.

This trend will continue until you run DBCC commands or restart SQL.
If your server must be up 24/7 then you will have to use one of the methods shown in the earlier link

There is some info here regarding connection pooling, DB performance, temp SPs and the like.
Not sure if it helps/applies.


Day1 15MB
Day2 25MB
Day3 40MB
DayX 200GB

Do you see traffic like that? Why the next day traffic is always heavier than the previous day?

Not me personally no.
Without knowing what the DBs/Apps do I couldn't possibly know but that looks like some fairly major activity.

Hi thomasjintao,

The temp db holds temp data:) what that means is that all your temp tables (some people call them hash tables ) you know the ones that start with # or ## are kept in temp db these tables are deleted as soon as they are no longer required so you may be creating a big temp table this grows tempdb but when you check its gone so lots of free space

all your group by and most of your order by queries results are housed in temp db lo lots of large queries like this will make it grow then when finished no longer there so lots of free space

in short your tempdb has at some time used that amount os space and from what you have said it will need it again.




Thanks regbes.

Most of the space in the tempdb is used by internal objects, not user objects. So temp tables in not our focus. We know our complicated queries and LOB parameters will cause the SQL server to use the tempdb. The problem is SQL server 2005 is not recycling the space properly, even all client connects are closed.
I changed the database to use READ_COMMITTED_SNAPSHOT, the tempdb stopped growing. It's either using less space in tempdb, or they are recycled properly in SNAPSHOT mode.
Is this a SQL server 2005 problem, or there's other reason?
monotring is the only way i can see to find the cause of this

can you run this evey say 10 mins in tempdb and save the results?

SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8  as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage

or in performance monitor these is a counter for free space in temp db run that  to check the allocation

more info here


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


Yes we ran it. As I mentioned before, the internal_object_reserved_page_count kept growing, and unallocated_extent_page_count kept dropping. Occationally there were some pages freed from  internal_object_reserved_page_count to unallocated_extent_page_count, but not all of them, which led to a steady space leak. When unallocated_extent_page_count  drops to 0, tempdb auto grows.
But after I set READ_COMMITTED_SNAPSHOT on, it looks good. Both unallocated_extent_page_count and internal_object_reserved_page_count become steady.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.