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
go select * from sys.dm_db_file_space_usage go select * from sys.dm_db_session_space_usage go 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