Printed 2017/08/23 08:12AM

What’s Using Space in Tempdb?

By Steve Jones, 2009/11/30

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
    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;

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.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.