Limiting SQL Temp DB Growth

Question

I am facing a serious issue in my production server where the temp DB grow exponantialy. Is there any way we can recover the tempDB space without restarting the SQL service?

Cheers Kannan.

Answer 1

I would ignore posts advising you to change the recovery model or limit the size of tempDB(!).

You need to track down the actual cause of the growth.

If you have the default trace turned on (it's on by default, out of the box), you can retrospectively find out what caused the growth by running this:

--check if default trace is enabled
if  exists (select 1 from sys.configurations where configuration_id = 1568)
BEGIN

declare @defaultTraceFilepath nvarchar(256)

--get the current trace rollover file
select @defaultTraceFilepath = CONVERT(varchar(256), value) from ::fn_trace_getinfo(0)
where property = 2

SELECT ntusername,loginname, objectname, e.category_id, textdata, starttime,spid,hostname, eventclass,databasename, e.name 
FROM ::fn_trace_gettable(@defaultTraceFilepath,0)
      inner join sys.trace_events e
            on eventclass = trace_event_id
       INNER JOIN sys.trace_categories AS cat
            ON e.category_id = cat.category_id
where 
      databasename = 'tempDB' and 
      cat.category_id = 2 and --database category
      e.trace_event_id in (92,93) --db file growth

END

Otherwise, you can start a SQL Profiler trace to capture these events. Turn on capturing of Auto Growth events, Sort Warnings and Join Warnings and look for cross joins, hash joins or missing join conditions.

SQL Server exposes a way to identify tempDB space allocations by currently executing queries, using DMVs:

-- This DMV query shows currently executing tasks and tempdb space usage
-- Once you have isolated the task(s) that are generating lots 
-- of internal object allocations, 
-- you can find out which TSQL statement and its query plan 
-- for detailed analysis
select top 10
  t1.session_id, 
  t1.request_id, 
  t1.task_alloc,
  t1.task_dealloc,  
  (SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
          (CASE WHEN statement_end_offset = -1 
              THEN LEN(CONVERT(nvarchar(max),text)) * 2 
                   ELSE statement_end_offset 
              END - t2.statement_start_offset)/2)
     FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
 (SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) as query_plan
from      (Select session_id, request_id,
sum(internal_objects_alloc_page_count +   user_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count) as task_dealloc
       from sys.dm_db_task_space_usage 
       group by session_id, request_id) as t1, 
       sys.dm_exec_requests as t2
where t1.session_id = t2.session_id and 
(t1.request_id = t2.request_id) and 
      t1.session_id > 50
order by t1.task_alloc DESC

(Ref.)

Answer 2

In SIMPLE mode, the tempdb database's log is constantly being truncated, and it can never be backed up. So check it is in Simple Mode

Answer 3

You can use DBCC SHRINKFILE to shrink the tempdb files and recover some space.

DBCC SHRINKFILE ('tempdev', 1) DBCC SHRINKFILE ('templog', 1)

The filenames can be found in the sysfiles table.

You still need to discover the root cause, but this can give you some breathing room until you do. The amount of space you recover will depend on usage and other factors.

Also:

How to shrink the tempdb database in SQL Server

http://support.microsoft.com/kb/307487