TempDB 內部空間使用量從服務代理緩慢增長

我正在SQL Server 2012 (11.0.6540.0)與另一台伺服器一起在 AO 高可用性組中執行。

tempDB 的使用量通常約為 819MB（從 2 週前開始平均超過一周），但由於我們進行了故障轉移和更新檔週期，tempDB 開始逐漸增長（此時沒有進行 DDL 更改）。我們剛剛完成了測試故障轉移，並將 tempDB 從兩台機器都可以訪問的聯合 SAN 驅動器中移出（我們不希望有一個硬體可以同時關閉我們的主副本和輔助副本）。

最近的故障轉移和實例重啟（以移動 tempDB）看到 tempDB 使用量躍升至 6348MB，並且還在緩慢增長。

增長都在內部對像上，可以使用：

SELECT   top 5 session_id, request_id, 
        SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count
FROM     sys.dm_db_task_space_usage 
GROUP BY session_id, request_id
ORDER BY request_internal_objects_alloc_page_count DESC

調查一下，這一切都是從與服務代理相關的 spid 35 和 32 執行的。

我環顧四周，有人說這是由於WITH CLEANUP結束了對話，但清理這個詞只出現在評論中，所以我相信這不是問題。

當實例恢復並接管主節點時，服務代理無法正常工作，所以我需要將所有隊列循環到禁用然後啟用狀態（我們必須在一年前這樣做一次，但沒有看到這個問題）。

您可以在此處查看 tempDB 上的使用情況，因為它目前在過去 4 天中保持不變。今天早上的下降是實例重啟和故障轉移。

有誰知道我缺少什麼才能讓這種情況重新得到控制。

TLDR；檢查對話是否完全打開。

在我們的系統中，我們重用了對話並有一個專門用於保存這些可用對話的表格，但是開發團隊在我離開的時候在我不知情的情況下設置了一個新的服務代理，沒有設置這些對話點並且沒有’不要在警報上設置任何門檻值。

當新系統打開時，會話正在打開但沒有正確關閉，並且由於池中沒有任何會話，它只是在創建一個新會話（我們為一個服務代理達到了 710 萬個會話）

我的修復步驟是創建並記錄該服務代理所需的 20 個對話處理程序，並將它們記錄到我們的表中。這阻止了 tempDB 的增長，以阻止 DB 下降的風險。

然後是關閉所有未使用的對話的漫長過程

select 
   se.[conversation_handle] 
from 
   sys.conversation_endpoints se with (nolock) 
   left outer join [queue].[maintained_Conversations] qh with (nolock) on qh.[conversation_handle] = se.[conversation_handle]
where 
   qh.service_name is not null 
   and se.far_service = @service

通過這些值提供所有 ID 游標的列表，並簡單地END CONVERSATION @id;在每個 ID 上執行。

程序完成後，臨時數據庫空間將放鬆（請注意，當您關閉它們時它不會這樣做，當您不再致力於創建/結束它們時，它似乎會大塊地進行（我不能保證這是它是如何工作的，正是我在停止中間程序和 tempDB 獲得一些空間後觀察到的）

引用自：https://dba.stackexchange.com/questions/149886