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?


3 Answers 3


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.

  • 4
    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
    Commented Feb 26, 2012 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

I am using Query Store

Select the Metric as "Temp DB Memory Used (KB)" and Statistic as "Max".

Then I found the boy who kill my TempDB.

update : I have checked the Query store feature, it is not available for SQL Server 2008.

enter image description here

  • We enabled Query Store but we can't see Temp DB Memory Used in the drop-down. How did you make it appear in there @Tajmahals? Commented Jun 12, 2020 at 1:40
  • @AkiraYamamoto I believed the TempDB measure are available since SQL Server 2016. The SQL Server 2014 may not.
    – Tajmahals
    Commented Jun 16, 2020 at 8:05
  • tks, we are using SQL Server 2016 tho Commented Jun 17, 2020 at 0:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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