tempdb 磁碟空間不足的疑難排解
此主題提供了程序和建議，可幫助您診斷和疑難排解因 tempdb 資料庫磁碟空間不足而造成的問題。tempdb 的磁碟空間用完會造成 SQL Server 實際執行環境嚴重中斷，並會讓執行中的應用程式無法完成作業。
tempdb 空間需求
tempdb 系統資料庫是全域資源，可供所有連接到 SQL Server 執行個體的使用者使用。tempdb 資料庫是用來儲存下列物件：使用者物件、內部物件和版本存放區。在 SQL Server 2005 中，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 頁給內部物件，且未取消配置任何一頁。這指出有潛在的問題。
.gif "ms176029.note(zh-tw,SQL.90).gif") 附註： |
|---|
| 身為資料庫管理員的您，可能會決定以小於 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 的容量計劃