Solved

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

Posted on 2006-11-02
24
1,943 Views
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.
0
Comment
Question by:thomasjintao
  • 9
  • 7
  • 2
  • +2
24 Comments
 
LVL 11

Expert Comment

by:rw3admin
Comment Utility
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
0
 

Author Comment

by:thomasjintao
Comment Utility
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.
0
 
LVL 29

Expert Comment

by:QPR
Comment Utility
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.


0
 
LVL 13

Expert Comment

by:Wizilling
Comment Utility
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.
0
 

Author Comment

by:thomasjintao
Comment Utility
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.
0
 
LVL 29

Expert Comment

by:QPR
Comment Utility
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.
0
 
LVL 13

Expert Comment

by:Wizilling
Comment Utility
0
 

Author Comment

by:thomasjintao
Comment Utility
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?

0
 
LVL 29

Expert Comment

by:QPR
Comment Utility
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.
0
 

Author Comment

by:thomasjintao
Comment Utility
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?  
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 29

Expert Comment

by:QPR
Comment Utility
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.
0
 

Author Comment

by:thomasjintao
Comment Utility
Then why it's growing every day?
0
 

Author Comment

by:thomasjintao
Comment Utility
Unless they are not freed because of the connection pooling. And sp_spaceused is cheating.
0
 
LVL 29

Expert Comment

by:QPR
Comment Utility
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
http://support.microsoft.com/kb/307487
0
 
LVL 29

Expert Comment

by:QPR
Comment Utility
There is some info here regarding connection pooling, DB performance, temp SPs and the like.
http://www.sql-server-performance.com/odbc_oledb.asp
Not sure if it helps/applies.
0
 

Author Comment

by:thomasjintao
Comment Utility
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?
0
 
LVL 29

Expert Comment

by:QPR
Comment Utility
Not me personally no.
Without knowing what the DBs/Apps do I couldn't possibly know but that looks like some fairly major activity.
0
 
LVL 11

Expert Comment

by:regbes
Comment Utility
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.

HTH

R.
0
 

Author Comment

by:thomasjintao
Comment Utility
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?
0
 
LVL 11

Accepted Solution

by:
regbes earned 500 total points
Comment Utility
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?

SELECT
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

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx#_Monitoring_space
0
 

Author Comment

by:thomasjintao
Comment Utility
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.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Determine next b-weekly date 12 52
ASP SQL Syntax Duplicate Key 7 63
MS SQL Backup 24 67
Date conversion in sql server 2012 6 23
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now