Solved

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

Posted on 2006-11-02
24
1,979 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
ID: 17862882
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
ID: 17862953
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
ID: 17863042
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 13

Expert Comment

by:Wizilling
ID: 17863050
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
ID: 17863359
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
ID: 17863474
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
ID: 17863510
0
 

Author Comment

by:thomasjintao
ID: 17863563
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
ID: 17863581
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
ID: 17863610
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
 
LVL 29

Expert Comment

by:QPR
ID: 17863771
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
ID: 17863798
Then why it's growing every day?
0
 

Author Comment

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

Expert Comment

by:QPR
ID: 17863819
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
ID: 17863828
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
ID: 17863851
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
ID: 17863882
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
ID: 17864490
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
ID: 17867492
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
ID: 17868806
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
ID: 17869189
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

809 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