SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

What’s Using Space in Tempdb?

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.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by srinath.m on 23 June 2011

Nice post....

Leave a Comment

Please register or log in to leave a comment.