發行項
09/24/2010

tempdb 磁碟空間不足的疑難排解

此主題提供了程序和建議，可幫助您診斷和疑難排解因 tempdb 資料庫磁碟空間不足而造成的問題。tempdb 的磁碟空間用完會造成 SQL Server 實際執行環境嚴重中斷，並會讓執行中的應用程式無法完成作業。

tempdb 空間需求

tempdb 系統資料庫是全域資源，可供所有連接到 SQL Server 執行個體的使用者使用。tempdb 資料庫是用來儲存下列物件：使用者物件、內部物件和版本存放區。

您可以使用 sys.dm_db_file_space_usage 動態管理檢視，來監視 tempdb 檔案中的使用者物件、內部物件和版本存放區所使用的磁碟空間。另外，若要在工作階段或工作層級監視 tempdb 中的頁面配置或取消配置活動，您可以使用 sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage 動態管理檢視。這些檢視可用來識別使用大量 tempdb 磁碟空間的大型查詢、暫存資料表或資料表變數。

診斷 tempdb 磁碟空間問題

下表列出的錯誤訊息，指出 tempdb 資料庫的磁碟空間不足。這些錯誤可以在 SQL Server 錯誤記錄檔中找到，也可能傳回至任何執行中的應用程式。

錯誤	引發時間
1101 或 1105	任何工作階段都必須在 tempdb 中配置空間。
3959	版本存放區已滿。此錯誤通常出現在記錄檔中的 1105 或 1101 錯誤之後。
3967	版本存放區被強制壓縮，因為 tempdb 已滿。
3958 或 3966	交易無法在 tempdb 中找到必要的版本記錄。

當資料庫設為自動成長，且資料庫大小快速增加時，也會指出有 tempdb 磁碟空間問題。

監視 tempdb 磁碟空間

下列範例顯示如何判斷 tempdb 可用的空間量，以及版本存放區、內部物件和使用者物件所使用的空間。

判斷 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

監視查詢使用的空間

tempdb 空間使用問題中最常見的類型之一，是與使用大量空間的大型查詢有關。通常，此空間是用於內部物件，如工作資料表或工作檔案。雖然監視內部物件使用的空間可讓您知道使用了多少空間，但它不會直接識別正在使用該空間的查詢。

下列方法可幫助識別正在使用 tempdb 中大部份空間的查詢。第一個方法會檢查批次層級的資料，使用的資料量比第二個方法少。第二個方法則可用來識別耗用磁碟空間的特定查詢、暫存資料表或資料表變數，但必須收集更多資料才能得到答案。

方法 1：批次層級資訊

如果批次要求只包含少數查詢，而且其中只有一個是複雜的查詢，通常就有足夠資訊知道是哪一個批次 (而非特定查詢) 在耗用空間。

若要繼續使用此方法，必須將 SQL Server Agent 作業設定為使用輪詢間隔 (範圍約在幾分鐘之間)，從 sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage 動態管理檢視中輪詢。下列範例使用 3 分鐘的輪詢間隔。您必須從兩個檢視中輪詢，因為 sys.dm_db_session_space_usage 不包括目前使用中工作的配置活動。比較兩個時間間隔中配置的頁數差異，可讓您計算兩個間隔之間配置了多少頁數。

下列範例提供 SQL Server Agent 作業所需的查詢。

A. 取得每個工作階段中所有目前正在執行的工作中，內部物件所耗用的空間。

下列範例會建立檢視：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

B. 取得目前工作階段中執行中和已完成的工作中，內部物件所耗用的空間。

下列範例會建立檢視：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

假設這些檢視是以 3 分鐘的間隔來接受查詢，結果集將提供下列資訊。

在下午 5:00，自工作階段啟動之後，工作階段 71 已配置 100 頁及取消配置 100 頁。
在下午 5:03，自工作階段啟動之後，工作階段 71 已配置 20100 頁及取消配置 100 頁。

當您分析此資訊時，可得知在兩個測量之間：工作階段已配置 20,000 頁給內部物件，且未取消配置任何一頁。這指出有潛在的問題。

[!附註]

身為資料庫管理員的您，可能會決定以小於 3 分鐘的頻率更頻繁地輪詢。不過，如果查詢執行的時間少於 3 分鐘，查詢可能不會耗用 tempdb 的大量空間。

若要判斷在該時間內執行的批次，請使用 SQL Server Profiler 來擷取 RPC:Completed 和 SQL:BatchCompleted 事件類別。

除了使用 SQL Server Profiler，另一種方法是每隔 3 分鐘對所有工作階段執行一次 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;

方法 2：查詢層級資訊

有時候如果只看輸入緩衝區或 SQL Server Profiler 事件 SQL:BatchCompleted，不一定能得知是哪一個查詢佔用了 tempdb 的大部份磁碟空間。您可利用下列方法來找到答案，但這些方法需要收集比方法 1 所定義的程序更多的資料。

若要繼續使用此方法，請設定 SQL Server Agent 作業的作業，從 sys.dm_db_task_space_usage 動態管理檢視中輪詢。相較於方法 1，輪詢間隔應該短一點，一分鐘一次。使用短間隔的原因，是假如目前沒有執行查詢 (工作) 時，sys.dm_db_task_space_usage 不會傳回資料。

在輪詢查詢中，定義在 sys.dm_db_task_space_usage 動態管理檢視中的檢視會和 sys.dm_exec_requests 聯結，來傳回 sql_handle, statement_start_offset、statement_end_offset 和 plan_handle 資料行。

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

如果查詢計畫是在快取中，您可以隨時擷取查詢的 Transact-SQL 文字及 XML 執行程序表格式的查詢執行計畫。若要取得所執行查詢的 Transact-SQL 文字，請使用 sql_handle 值和 sys.dm_exec_sql_text 動態管理函數。若要取得查詢計畫執行，請使用 plan_handle 值和 sys.dm_exec_query_plan 動態管理函數。

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

如果查詢計畫不在快取中，您可以使用下列其中一種方法，來取得查詢的 Transact-SQL 文字和查詢執行計畫。

A. 使用輪詢方法

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

B. 使用 SQL Server Profiler 事件

除了輪詢 sys.dm_exec_sql_text 和 sys.dm_exec_query_plan 函數，另一個替代方法是使用 SQL Server Profiler 事件。有一些 Profiler 事件可用來擷取查詢計畫和產生的查詢文字。例如「事件 165」會傳回追蹤的效能統計資料、SQL 文字、查詢計畫和查詢統計資料。

監視暫存資料表和資料表變數所使用的空間

您可以使用類似輪詢查詢的方法，來監視暫存資料表和暫存變數所使用的空間。會取得暫存資料表或暫存變數內大量使用者資料的應用程式，會造成 tempdb 的空間使用問題。這些資料表或變數屬於使用者物件。您可以在 sys.dm_db_session_space_usage 動態管理檢視中使用 user_objects_alloc_page_count 和 user_objects_dealloc_page_count 資料行，並遵循使用前述的方法。

按工作階段監視頁面配置和取消配置

下表顯示 sys.dm_db_file_space_usage、sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage 動態管理檢視針對指定的工作階段傳回的結果。每一資料列代表指定的工作階段在 tempdb 中的配置或取消配置活動。活動都列在事件資料行中。其餘資料行顯示動態管理檢視的資料行中會傳回的值。

以此案例而言，假設 tempdb 資料庫開始時有 872 頁在未配置範圍，有 100 頁在使用者物件保留範圍。工作階段配置 10 頁給使用者資料表，然後再全部取消配置。前 8 頁是在混合範圍內。剩餘 2 頁是在制式範圍內。

事件	dm_db_file_space_usage unallocated_extent_page_count 資料行	dm_db_file_space_usage user_object_reserved_page_count 資料行	dm_db_session_space_usage and dm_db_task_space_usage user_object_alloc_page_count 資料行	dm_db_session_space_usage and dm_db_task_space_usage user_object_dealloc_page_count 資料行
啟動	872	100	0	0
從現有的混合範圍配置第 1 頁	872	100	1	0
配置第 2 到第 8 頁：耗用一個新的混合範圍	864	80	8	0
配置第 9 頁：耗用一個新的制式範圍	856	108	16	0
從現有的制式範圍配置第 10 頁	856	108	16	0
從現有的制式範圍取消配置第 10 頁	856	108	16	0
取消配置第 9 頁和制式範圍	864	100	16	8
取消配置第 8 頁	864	100	16	9
取消配置第 7 到第 1 頁，在混合範圍上取消配置	872	100	16	16

請參閱

概念

最佳化 tempdb 效能

tempdb 資料庫

tempdb 的容量計畫

其他資源

疑難排解 (Database Engine)

共用方式為

tempdb 磁碟空間不足的疑難排解

tempdb 空間需求

診斷 tempdb 磁碟空間問題

監視 tempdb 磁碟空間

判斷 tempdb 的可用空間量

判斷版本存放區使用的空間量

判斷執行最久的交易

判斷內部物件使用的空間量

判斷使用者物件使用的空間量

判斷總空間量 (可用和已用)

監視查詢使用的空間

方法 1：批次層級資訊

A. 取得每個工作階段中所有目前正在執行的工作中，內部物件所耗用的空間。

B. 取得目前工作階段中執行中和已完成的工作中，內部物件所耗用的空間。

方法 2：查詢層級資訊

A. 使用輪詢方法

B. 使用 SQL Server Profiler 事件

監視暫存資料表和資料表變數所使用的空間

按工作階段監視頁面配置和取消配置

請參閱

概念

其他資源

其他資源