Tempdb size large and growing

Question

Our server has limited storage and the tempdb appears to have a transaction (LSN) that is active and unable to commit or rollback (the log_reuse_wait_desc indicates that the tempdb is in Active_transaction status).  The tempdb size is double the size of our largest database.  With limited space on the disks, it puts us in a state of constant diligence - making certain that anything that can be removed is removed to maintain space for the database to grow.  The server is maxed out on disk size and though additional space has been ordered, it may not be approved.  

I am proposing that the solution suggested here: http://support.microsoft.com/kb/288809 

which says to put the database in the correct mode:  sqlservr -c -f -T3608 -T402

and then requires a statement be run:   exec master..sp_resetstatus Tempdb

is not the correct solution for this problem.  We are running into this problem on a production server.  There is no need to take risks by trying something that may or may not work.  The temp database must be reduced in size.  So this is mostly a question that doesn't get to the question at hand.  Not sure if this is a correct answer, need feedback.

It is also possible that there is an uncommitted tempdb spillover because a temp table was not dropped and the table remained open on the server. Given the latter, it would be possible to review stored procedures for temp tables that have not been dropped.

---

R, J

Answer 1

Hi,

I have split the question so that more people will look at this. As I understand, your server is still running although the size of the tempdb is twice your user database. If that is the case then KB you listed may not be appropriate action for you at this time.

Have you checked the size of the data and log file for tempdb? Have you checked if there are any open transactions? Tempdb is in simple recovery model and there are some cases where it doesn't CHECKPOINT after the log file is 70% used. sometimes manually issuing the CHECKPOINT command helps. Also look at which sessions are using the tempdb. I would recommend to narrow down the problem as much as possible using the queries listed below and if everything doesn't work then a simple re-start of the SQL service will re-create the tempdb to its normal size but you may still want to monitor tempdb. This is just a quick fix.

http://msdn.microsoft.com/en-us/library/ms176029.aspx

USE tempdb;
GO

-- Individual File Sizes and space available for current database
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB]
FROM sys.database_files OPTION (RECOMPILE);

---

Sankar Reddy

Blog: http://SankarReddy.com/
Twitter: http://twitter.com/SankarReddy13/

Answer 2

Adding to what Sankar mentioned, there can be n number of reasons of the temodb growth. Because we do lots of activity in tempdb e.g. version store maintenance, temp tables and sorting/hashing(if required) etc. I would like to check the summary of space usage in tempdb. Please run the query:

DECLARE @runtime datetime
SET @runtime = GETDATE()
select CONVERT (varchar(30), @runtime, 121) AS runtime, 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

Also, please check the sizes of ldf and mdf files. Many times ldf filesize is larger than mdf filesize.

---

Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
|Team Blog:- Team Blog

Answer 3

We are able to manage the size only by checkpointing the tempdb and rebooting the server.   We tried just checkpointing tempdb and restarting the service but it was an ineffective solution. 

We are fortunate enough that transactions are used rarely in the application and though I check regularly for locking, this portion of your suggestion does not appear to be at issue.  That said, the tempdb is always in ACTIVE_TRANSACTION mode when the tempdb swells up. 

Frankly, I think it is a disk-swap issue as the server has high ram capability, fast disk access (15000 rpm), and is running on 8 quads which is slightly over-powered but should be good for some time to come.  I suspect that it is possible a longer-running query at the wrong time, can trip cache to overflow to the tempdb...(whatever may be).  I have thought this one through many times.  I'm not really sure what causes the tempdb to swell up to two or three times the size of our largest mdb file.   Both the tempdb.mdf and the log file grow and it occurs very suddenly, not over time.

It would be nice to have an option other than kicking the server over to fix this problem.

SELECT log_reuse_wait_desc,Name FROM sys.databases

---

R, J

Answer 4

Sankar,

The solution you list works but only if the log file is under 70% which you explicitly state.  So given that what you say is correct, is there any method other than restarting the server (for example, a way to get notification before the log file hits 70% - which we seem to be unable to do) once the server has gone beyond that 70% limitation?

---

R, J

Answer 5

Hi,

Although I never had to do this on a production box, I knew from respectable people in the community that issuing the manual CHECKPOINT multiple times helped even after the log was 70% full.

>>a way to get notification before the log file hits 70% - which we seem to be unable to do<<

You could easily have a job that monitors the tempdb log space usage and fire off an email alert before it hits a threshold.

---

Sankar Reddy

Blog: http://SankarReddy.com/
Twitter: http://twitter.com/SankarReddy13/

Answer 6

I wish it were that simple (monitor the space).  We have a job sends out threshold information... the problem is that when the tempdb grows, it grows quickly; more akin to disk swaps in about 28 Gig (Machine is 32 Gig Ram with 4 Gig to the system and the rest to SQL) to the TempDB and log (both tempdb and log) and generally a second swap so the log goes from about 28 Meg to 60 Gig almost instantly.   When it happens on a weekend and no one is there to see it, it takes a while for people to notice the application has failed because the SQL Server has locked up...  we don't work weekends but the app still runs.   This is why I am hesitant to mark your answer as correct even though it works on one level.  But will definitely try the "MULTIPLE" checkpoint idea.  That's a good idea also.

There has to be a better solution.  We can fix it quickly when we are there, but not when we aren't there.  I can figure multi alerts... maybe one of the team will get it on a weekend.  I believe I tried sending the alert to a group email address and that failed.   Strange, that...  urrgggh

---

R, J

Answer 7

You could create an alert that fires when the log reaches 50% full and fires a job which executes the checkpoints or any otehr scripts you want to run.

This script will create the alert on teh log file - but do nothing. You can amend it for your purpose:

EXEC msdb.dbo.sp_add_alert @name=N'TempDB 50% full', 
		@message_id=0, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@category_name=N'[Uncategorized]', 
		@performance_condition=N'MSSQL:Databases|Percent Log Used|tempdb|>|50', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO



---

http://lqqsql.wordpress.com