It happens, not often, but I do regularly see people talking about their tempdb growing too large. Often that means that people have not properly sized tempdb to me, but there could be legitimate reasons why it’s grown large.
This is one reason that I’d like to see a separate tempdb for each database, a way that would allow us to better understand what level of resource usage is needed by a particular database.
So how do you determine what’s using tempdb? I found this great entry in Books Online, Troubleshooting Insufficient Disk Space in tempdb, that contains a bunch of queries to help you determine what’s wrong. There’s one to get the free space
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
and one that finds the longest running transaction
SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
and one that looks at the usage by session
CREATE VIEW all_task_usage
AS
SELECT session_id,
SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;
GO
There are a few more, but if you’re wondering what’s happening in tempdb, this is a good article to go through and use some of these queries to diagnose what’s happening on your instance.
