SqlServer 一個查詢語句導致tempdb增大55G

標籤：

今天操作著伺服器，突然右下角提示“C盤空間不足”！

嚇一跳！~

看看C盤，還有7M！！！這麼大的C盤空間怎麼會沒了呢?搞不好等下伺服器會動不了!

第一反應就想可能是日誌問題,很可能是資料庫日誌問題

於是查看日誌，都不大，正常。

dbcc sqlperf(logspace)

看看系統報錯：

是tempdb問題，但是剛才看日誌才幾M，根據提示查看日誌狀態：

select name,log_reuse_wait_desc from sys.databases 

資料庫日記現在沒什麼操作，可能是執行完了。

活動的虛擬日誌也不多，10個左右：

dbcc loginfo

查看當前tempdb情況，嚇一跳啊，tempdb資料檔案55G！ 看上面的圖，也就是突然增長的。

於是馬上收縮日誌，收縮資料檔案，收縮出1G左右。

還是不行，繼續不斷地更改大小不斷收縮，只要小於55G都改資料進行收縮，竟然還能收縮了9G！

DBCC SHRINKFILE (N'tempdev' , 1024)--單位為MBDBCC SHRINKDATABASE (tempdb, 1024);--單位為MB

暫時緩解了，看來是收縮不了了。都說得重啟伺服器才行，當前串連較多，沒有重啟.所以先查查什麼原因引起的。

查看當前的各種遊標，SQL ，堵塞等，沒發現什麼，事務應該執行完了。

查看tempdb記錄的分配情況：

use tempdbgoSELECT top 10 t1.session_id,                                                    t1.internal_objects_alloc_page_count,  t1.user_objects_alloc_page_count,t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count,t3.login_name,t3.status,t3.total_elapsed_timefrom sys.dm_db_session_space_usage  t1 inner join sys.dm_exec_sessions as t3 on t1.session_id = t3.session_id where (t1.internal_objects_alloc_page_count>0 or t1.user_objects_alloc_page_count >0or t1.internal_objects_dealloc_page_count>0 or t1.user_objects_dealloc_page_count>0)order by t1.internal_objects_alloc_page_count desc

有四個關鍵資訊：

session_id ：稍等可以查詢該session的相關資訊

internal_objects_alloc_page_count  ：分配給session內部對象的資料頁

internal_objects_dealloc_page_count ：已經釋放的資料頁

login_name : 該session的登入名稱

從internal_objects_alloc_page_count  和internal_objects_dealloc_page_count可以看出，給session分配了7236696頁，計算一下：

select 7236696*8/1024/1024 as [size_GB]

竟然為55G，幾乎和tempdb增長的大小一致，可以斷定就是這個session引起的。internal_objects_dealloc_page_count 可以看到已經釋放了，暫用tempdb的資料已經釋放了。

通過登入名稱，已經知道誰在操作了。（這就是給每個相關人員自己登入名稱的好處之一，可以很快追蹤使用者，是內部人員操作）

現在看看這session_id的用處：

select p.*,s.text from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) swhere spid = 1589

看到最後有一條語句：

拷貝出來，幾乎是資料庫中最大的 8個表做inner join 串連 查詢！！

代碼就不貼出來了。

目前已經查出什麼原因導致了tempdb增大的問題。tempdb從55285MB收縮為47765MB，但大小問題得晚點重啟服務在看看了。

SqlServer 一個查詢語句導致tempdb增大55G