Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

The tempdb of a server (SQL Server 2008) increases to 500GB+ several times every month. Is it possible to find out which SQL statements caused this problem? The problem is usually not caused by create table #temp...; insert into #temp... or select ... into #temp... but complex joins.

The Initial Size of some of the tempdb files is also automatically set to much bigger values every time. How to prevent it?

Sometime the cached plans prevent resizing/shrinking the files. How to find which one hold the tempdb?

share|improve this question
Sorry it's almost 2 AM and I'm out of gas to answer this question completely, however these URLs may come in handy while you wait for other answers -… and – Aaron Bertrand Feb 25 '12 at 6:39
up vote 26 down vote accepted

There are three DMVs you can use to track tempdb usage:

The first two will allow you to track allocations at a query & session level. The third tracks allocations across version store, user and internal objects.

The following example query will give you allocations per session:

  sys.dm_exec_sessions.session_id AS [SESSION ID]
  ,DB_NAME(database_id) AS [DATABASE Name]
  ,HOST_NAME AS [System Name]
  ,program_name AS [Program Name]
  ,login_name AS [USER Name]
  ,cpu_time AS [CPU TIME (in milisec)]
  ,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
  ,total_elapsed_time AS    [Elapsed TIME (in milisec)]
  ,(memory_usage * 8)      AS [Memory USAGE (in KB)]
  ,(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
  ,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
  ,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
  ,(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
  ,CASE is_user_process
             WHEN 1      THEN 'user session'
             WHEN 0      THEN 'system session'
  END         AS [SESSION Type], row_count AS [ROW COUNT]
INNER join
ON  sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

If you want to track usage over a period of time, consider collecting data with sp_whoisactive, as demonstrated by Kendra Little.

share|improve this answer
Thanks. Is the difference of [SPACE Allocated FOR USER Objects (in KB)] and [SPACE Deallocated FOR USER Objects (in KB)] the actual occupied space of the session? – u23432534 Feb 26 '12 at 2:22

There can be various sources of a problem:

  • usage of table variables or temporary tables
  • sql server created intermediate resultsets as worktables in tempdb - usually for sorting purposes (usually is a sign of absent indexes/out-of-date statistics)
  • sql server decided to pre-evaluate the resultset of table valued function and in this case it stores the data in tempdb
  • recreating indexes with option SORT_IN_TEMPDB = ON
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.