Monitor SQL Server TempDB usage

07 March,2013 by Jack Vamvas

Large queries spilling into tempdb such as SQL Server – SORT IN TEMPDB and Sort Warnings  and large temporary tables can use Tempdb space very quickly and lead to outages. Use the DMVs : sys.dm_db_file_space_usage, sys.dm_db_session_space_usage, sys.dm_db_task_space_usage

How to Troubleshoot TempDB

First Step - Comprehensive Tempdb Sizing exercise. There are many different methods  , such as : a) create a workload b) running large maintenance jobs e.g index rebuild

Second Step - monitoring Tempdb usage. As well as a  SQL Server monitoring alerts solution based on events , such as disk full, there are some useful DMVs  to drill into TempDB usage based on tasks and sessions.

sys.dm_db_file_space_usage -  Lists space usage information for each file in the Tempdb database. Reports on unallocated extent page count.

sys.dm_db_session_space_usage – Broken down by each session. Lists the number of pages allocated and deallocated

sys.dm_db_task_space_usage – Broken down by each task. Lists page allocation and deallocation activity


select * from  sys.dm_db_file_space_usage 
select * from  sys.dm_db_session_space_usage
select * from sys.dm_db_task_space_usage

To see the type of objects filling the TempDB  use this  SQL Script - How to find type of objects filling TempDB


Read More on TempDB performance strategies

SQL Server – List current tempdb statements

SQL Server - TempDB performance and strategy checklist

SQL Server – tempdb and solid state drives

SQL Server - Shrink tempdb without sql server restart


Author: Jack Vamvas (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Monitor SQL Server TempDB usage | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer