查找哪個會話持有哪個臨時表

我們有一個 SQL Server 2005 數據庫，臨時數據庫已滿。通過進入 SQL Server Management Studio，我可以看到 tempdb 中的所有臨時表。是否可以判斷哪個會話持有哪個臨時表？理想情況下，查詢會列出每個會話使用的臨時表。

謝謝，

早在 2007 年，我就要求在 Connect 上建構一些東西。這在 2008 年版本中被拒絕，隨後被忽略，直到幾年前 Connect 死了。我試圖在 SQL Server 的新回饋站點上找到它]（它也已被殺死，但該搜尋絕對是垃圾箱之火。我請求的標題是“dmv 將臨時表映射到 session_id” - 因為搜尋可以只做 OR，“map temp table”返回 118 頁的結果。Google似乎暗示該項目在他們殺死 Connect 時沒有成功。

同時，對於 SQL Server 2005 和 2008，您應該能夠從預設跟踪中提取此資訊：

DECLARE @FileName VARCHAR(MAX)  

SELECT @FileName = SUBSTRING(path, 0,
  LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT   
    o.name,   
    o.OBJECT_ID,  
    o.create_date, 
    gt.NTUserName,  
    gt.HostName,  
    gt.SPID,  
    gt.DatabaseName,  
    gt.TEXTData 
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt  
JOIN tempdb.sys.objects AS o   
    ON gt.ObjectID = o.OBJECT_ID  
WHERE gt.DatabaseID = 2 
 AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)  
 AND o.create_date >= DATEADD(ms, -100, gt.StartTime)   
 AND o.create_date <= DATEADD(ms, 100, gt.StartTime)

*無恥地從Jonathan Kehayias 的這篇博文*中解脫出來。

要確定空間使用情況，您可以進一步增強它以加入來自視圖的數據，sys.db_db_partition_stats例如：

DECLARE @FileName VARCHAR(MAX)  

SELECT @FileName = SUBSTRING(path, 0,
  LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT   
    o.name,   
    o.OBJECT_ID,  
    o.create_date, 
    gt.NTUserName,  
    gt.HostName,  
    gt.SPID,  
    gt.DatabaseName,  
    gt.TEXTData,
    row_count = x.rc,
    used_page_count = x.upc
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt  
JOIN tempdb.sys.objects AS o   
    ON gt.ObjectID = o.OBJECT_ID
INNER JOIN
(
SELECT [object_id], SUM(row_count), SUM(used_page_count)
  FROM tempdb.sys.dm_db_partition_stats
  WHERE index_id IN (0,1)
  GROUP BY [object_id]
) AS x(id, rc, upc)
ON x.id = o.[object_id]
WHERE gt.DatabaseID = 2 
 AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)  
 AND o.create_date >= DATEADD(ms, -100, gt.StartTime)   
 AND o.create_date <= DATEADD(ms, 100, gt.StartTime)

這裡的問題是試圖通過查詢文本關聯表名；這是不切實際的，因為大多數時候，使用者還沒有對該表執行查詢（沒關係仍然執行創建/填充它的那個）。

但是，這適用於其他讀者（或升級時的您），如果 #temp 表是堆，則**2012+ 中的預設跟踪不再跟踪臨時表對象創建。**不確定這是巧合還是與從 2012 年開始所有臨時表現在都為負數object_id的事實直接相關。您當然可以轉到擴展事件來幫助您收集和跟踪這些資訊，但這可能需要大量的手動工作（而且我只是驗證了這不再在跟踪中被跟踪 - 您可能無法選擇它在擴展事件中也可以）。預設跟踪將選擇使用 PK 或其他約束創建的 #temp 表，或者在創建事件之後添加約束或索引，但是您必須放寬上述基於時間的限制（可以在 100 毫秒之後創建索引創建）。

本網站上的其他一些可能有用的答案：

• 如何確定哪個查詢正在填滿 tempdb 事務日誌？
• TempDB mdf 文件的問題不斷增加
• 查找正在填滿版本儲存的事務

我還寫了一篇關於此的部落格，使用自定義擴展事件會話來跟踪 SQL Server 2012 及更高版本中的此資訊：

• 伙計，誰擁有那個#temp 表？

Paul White 寫過關於直接閱讀頁面的部落格（不完全適合膽小的人，也不容易以任何方式自動化）：

• 查看另一個會話的臨時表

這是一個查詢，可以幫助您開始查找您正在尋找的資訊：

select top 10
   tsu.session_id,
   tsu.request_id,
   r.command,
   s.login_name,
   s.host_name,
   s.program_name,
   total_objects_alloc_page_count = 
       tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count,
   tsu.user_objects_alloc_page_count,
   tsu.user_objects_dealloc_page_count,
   tsu.internal_objects_alloc_page_count,
   tsu.internal_objects_dealloc_page_count,
   st.text
from sys.dm_db_task_space_usage tsu
inner join sys.dm_exec_requests r
on tsu.session_id = r.session_id
and tsu.request_id = r.request_id
inner join sys.dm_exec_sessions s
on r.session_id = s.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
where tsu.user_objects_alloc_page_count > 0
or tsu.internal_objects_alloc_page_count > 0
order by total_objects_alloc_page_count desc;

此查詢提取前 10 個任務的有用資訊，例如分配/取消分配的頁面、任務的 SQL 文本（如果可用）等。

這些 DMV 充滿了重要的資訊，因此如果您需要更多數據，那麼您可以混合和匹配您正在提取的內容。但這應該是對目前 tempdb 消耗任務進行故障排除的起點。

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