The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores.

You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space used by the user objects, internal objects, and version stores in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using a large amount of tempdb disk space.

The following table lists error messages that indicate insufficient disk space in the tempdb database. These errors can be found in the SQL Server error log, and may also be returned to any running application.

tempdb disk space problems are also indicated when the database is set to autogrow, and the size of the database is quickly increasing.

The following examples show how to determine the amount of space available in tempdb, and the space used by the version store and internal and user objects.

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;

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;

SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;

SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files

One of the most common types of tempdb space usage problems is associated with large queries that use a large amount of space. Generally, this space is used for internal objects, such as work tables or work files. Although monitoring the space used by internal objects tells you how much space is used, it does not directly identify the query that is using that space.

The following methods help identify the queries that are using the most space in tempdb. The first method examines batch-level data and is less data intensive than the second method. The second method can be used to identify the specific query, temp table, or table variable that is consuming the disk space, but more data must be collected to obtain the answer.

Method 1: Batch-Level Information

If the batch request contains just a few queries, and only one of them is a complex query, this is typically enough information to know just which batch is consuming the space instead of the specific query.

To continue with this method, a SQL Server Agent Job must be set up to poll from the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views by using a polling interval in the range of few minutes. A polling interval of three minutes is used in the following example. You must poll from both views because sys.dm_db_session_space_usage does not include the allocation activity of the current active task. Comparing the difference between the pages allocated at two time intervals lets you calculate how many pages are allocated in between the intervals.

The following examples provide the queries that are required for the SQL Server Agent job.

A. Obtaining the space consumed by internal objects in all currently running tasks in each session.

The following example creates the view all_task_usage. When queried, the view returns the total space used by internal objects in all currently running tasks in tempdb.

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

B. Obtaining the space consumed by internal objects in the current session for both running and completed tasks

The following example creates the view all_session_usage. When queried, the view returns the space used by all internal objects running and completed tasks in tempdb.

CREATE VIEW all_session_usage 
AS
    SELECT R1.session_id,
        R1.internal_objects_alloc_page_count 
        + R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
        R1.internal_objects_dealloc_page_count 
        + R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
    FROM sys.dm_db_session_space_usage AS R1 
    INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;
GO

Assume that when these views are queried at a three-minute interval, the result sets provide the following information.

• At 5:00 P.M., session 71 allocated 100 pages and deallocated 100 pages since the start of the session.
  
• At 5:03 P.M., session 71 allocated 20100 pages and deallocated 100 pages since the start of the session.
  

When you analyze this information, you can tell that between the two measurements: The session allocated 20,000 pages for internal objects, and did not deallocate any pages. This indicates a potential problem.

Note:
As the database administrator, you may decide to poll more frequently than three minutes. However, if a query runs for less than three minutes, the query probably will not consume a significant amount of space in tempdb.

To determine the batch that is running during that time, use SQL Server Profiler to capture the RPC:Completed and SQL:BatchCompleted event classes.

An alternative to using SQL Server Profiler is to run DBCC INPUTBUFFER once every three minutes for all the sessions, as shown in the following example.

DECLARE @max int;
DECLARE @i int;
SELECT @max = max (session_id)
FROM sys.dm_exec_sessions
SET @i = 51
  WHILE @i <= @max BEGIN
         IF EXISTS (SELECT session_id FROM sys.dm_exec_sessions
                    WHERE session_id=@i)
         DBCC INPUTBUFFER (@i)
         SET @i=@i+1
         END;

Method 2: Query-Level Information

CREATE VIEW all_request_usage
AS 
  SELECT session_id, request_id, 
      SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count 
  FROM sys.dm_db_task_space_usage 
  GROUP BY session_id, request_id;
GO
CREATE VIEW all_query_usage
AS
  SELECT R1.session_id, R1.request_id, 
      R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,
      R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle
  FROM all_request_usage R1
  INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;
GO

SELECT * FROM sys.dm_exec_sql_text(@sql_handle);
SELECT * FROM sys.dm_exec_query_plan(@plan_handle);

SELECT R1.sql_handle, R2.text 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2;

SELECT R1.plan_handle, R2.query_plan 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_query_plan(R1.plan_handle) AS R2;

You can use an approach similar to polling queries for monitoring the space used by temp tables and temp variables. Applications that acquire a large amount of user data inside temp tables or temp variables can cause space use problems in tempdb. These tables or variables belong to the user objects. You can use the user_objects_alloc_page_count and user_objects_dealloc_page_count columns in the sys.dm_db_session_space_usage dynamic management view and follow the methods described earlier.

The following table shows the results returned by the sys.dm_db_file_space_usage, sys.dm_db_session_space_usage, and sys.dm_db_task_space_usage dynamic management views for a specified session. Each row represents an allocation or deallocation activity in tempdb for a specified session. The activity is listed in the Event column. The remaining columns show the values that would be returned in the columns of the dynamic management views.

For this scenario, assume that the tempdb database starts with 872 pages in unallocated extents, and 100 pages in user-object reserved extents. The session allocates 10 pages for a user table, and then deallocates all of them. The first 8 pages are in mixed extent. The remaining 2 pages are in uniform extent.

Other Resources

Help and Information