msdb tempdb usage

Question

I am checking about tempdb size.

What I checked is

1. Data file usage - sys.dm_db_file_space_usage
2. Log file usage - sys.dm_db_log_space_usage
3. Table size - sys.dm_partition_stats, sys.dm_internal_tables, sys.all_objects
4. Tempdb usage by session - sys.dm_db_task_space_usage, sys.dm_exec_request, sys.dm_exec_sessions

etc. information was used.

Inside sys.dm_db_file_space_usage, user object usage and sys.dm_db_task_space_usage usage are different. Is there anything else I should check?

Answer 1

Hi @Chaewon,

To check current size and growth parameters for tempdb, use the following query:

SELECT FileName = df.name,
   current_file_size_MB = df.size*1.0/128,
   max_size = CASE df.max_size
     WHEN 0 THEN 'Autogrowth is off.'
     WHEN -1 THEN 'Autogrowth is on.'
     ELSE 'Log file grows to a maximum size of 2 TB.'
   END,
   growth_value =
     CASE
       WHEN df.growth = 0 THEN df.growth
       WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN df.growth*1.0/128.0
       WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN df.growth
     END,
   growth_increment_unit =
     CASE
       WHEN df.growth = 0 THEN 'Size is fixed.'
       WHEN df.growth > 0 AND df.is_percent_growth = 0  THEN 'Growth value is MB.'
       WHEN df.growth > 0 AND df.is_percent_growth = 1  THEN 'Growth value is a percentage.'
     END
FROM tempdb.sys.database_files AS df;
GO

You may also refer to this blog about monitoring the SQL Server tempdb database.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.