解决 tempdb 中磁盘空间不足的问题

下面的查询将返回 tempdb 中所有文件的总可用页数和总可用空间量 (MB)。

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;

下面的查询将返回 tempdb 中版本存储区使用的总页数和总空间量 (MB)。

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;

如果版本存储区使用了 tempdb 中的大量空间，则必须确定运行时间最长的事务。使用下面的查询可按顺序（事务的最长运行时间）列出活动事务。

SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

与联机索引操作无关的长时间运行的事务需要很大的版本存储区。此版本存储区保存自事务启动以来生成的所有版本。联机索引生成事务可能需要较长时间才能完成，但是使用了专用于联机索引操作的单独的版本存储区。因此，这些操作不会防止删除其他事务的版本。有关详细信息，请参阅行版本控制资源的使用情况。

下面的查询将返回 tempdb 中内部对象使用的总页数和总空间量 (MB)。

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;

下面的查询将返回 tempdb 中用户对象使用的总页数和总空间量。

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;

下面的查询将返回 tempdb 中所有文件使用的磁盘空间总量。

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

下面的示例创建视图 all_task_usage。执行查询后，视图将返回 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

下面的示例创建视图 all_session_usage。执行查询后，视图将返回 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

假设查询这些视图的时间间隔为三分钟，结果集将提供下列信息。

• 在下午 5:00 时，会话 71 自会话开始后分配了 100 页并释放了 100 页。

• 在下午 5:03 时，会话 71 自会话开始后分配了 20100 页并释放了 100 页。

分析此信息时，您可以由这两个度量得出以下结论：会话为内部对象分配了 20,000 页，并且没有释放任何页。这指示了存在一个潜在问题。

注意
作为数据库管理员，您可以决定将轮询的时间间隔改为小于三分钟。但是，如果查询运行时间少于三分钟，则该查询可能不会占用 tempdb 中的大量空间。

若要确定在此期间运行的批处理，请使用 SQL Server 事件探查器来捕获 RPC:Completed 和 SQL:BatchCompleted 事件类。

除了使用 SQL Server Profiler之外，还可以选择每三分钟为所有会话运行一次 DBCC INPUTBUFFER，如下面的示例所示。

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;

从视图 all_query_usage 轮询，并运行下面的查询以获得查询文本：

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

由于 sql_handle 对每个唯一的批处理都应该是唯一的，因此不必保存重复的 sql_handle 项。

若要保存计划句柄和 XML 计划，请运行下面的查询。

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;

除了轮询 sys.dm_exec_sql_text 和 sys.dm_exec_query_plan 函数之外，还可以使用 SQL Server Profiler事件。有一些事件探查器事件可用于捕获查询计划和生成的查询文本。例如，事件 165 将返回跟踪、SQL 文本、查询计划和查询统计信息的性能统计信息。