[SQL][問題處理]肥得要死的 tempdb 如何縮減

[SQL][問題處理]肥得要死的 tempdb 如何縮減

這幾天有朋友問到,如果 SQL Server 資料庫太大了,要如何縮減 ? 看起來應該不算是個困難的問題,一般來說如果不想去背指令的話,可以利用 SSMS 先挑選欲處理的資料庫,按下滑鼠右鍵選擇「工作」→「壓縮」→「資料庫

image

image

image

 

但如果你嫌這樣不夠專業,那麼你想使用指令也是可以的啦 !

DBCC SHRINKDATABASE(N'TSST' )
GO

 

原本想說這樣就解決問題了,但朋友長大的資料庫是 tempdb,已經成長到 50GB。但不論是透過 GUI 或者是指令碼的方式,朋友測試好幾次都無法有效地縮減大小。針對這樣的狀況,我們會先查看一下 tempdb 的使用,看起來並沒有甚麼物件存放在裡面,而且也檢查目前正在運作中的連線和交易,似乎都沒有佔用 tempdb 的狀況 ( 可參考 [SQL][Performance]是誰佔用 tempdb ? )。長久以來他們都是把 SQL Server 的服務重新啟動,雖然這也是一種可以解決問題的方式,但這樣可能會造成一些長時間連接資料庫的程式有異常,因此會希望能找到一個不需要重新啟動資料庫服務的方式。

 

至於 tempdb 的用途,可以參考 Technet 上的文章,一般來說當你連線結束之後,所佔用的資源應該會被釋放,但目前他們的 tempdb 會長到那麼大的話,我們直接使用壓縮指令又無法壓縮的狀況下,因此建議朋友先使用以下的指令來試試看:

-- 避免還有資料還在記憶體內,手動進行 Checkpoint
Checkpoint
GO
  
-- 強制釋放佔用的記憶體
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
DBCC FREESESSIONCACHE
GO

 

完成上述指令之後,再重新進行一次壓縮 tempdb,果然就可以順利把 tempdb 給壓縮了。

DBCC SHRINKDATABASE(N'tempdb')

 


 

雖然暫時解決 tempdb 過大的問題,但實際問題還是沒有解決,為什麼 tempdb 會長到那麼大的狀況呢 ? 此部分後來透過查看 SQL Server Error Log 和 SQL Server Agent Log 相互比對的結果,有可能是以下幾個原因所造成的

1. 因為發生長大的時候都是 SSIS 正在大量處理資料的時候,此時因為使用 LINK Server 的機制,導致會大量使用 tempdb 來做暫存。

2. 在 SSIS 內同時運行許多耗時的 Stored Procedure,這些 SP 都會大量產生 temp 物件,而這些物件大量使用之見,所佔用的 Cache 空間並沒有隨著 Stored Procedure 結束而釋放。

3. 在異常發生時也在進行資料庫備份,同時間可能大量的處理也讓 SQL Server 需要大量的記憶體

 

因此為了避免狀況惡化,因此建議做一些調整:

1. 將 SSIS 和 SSIS 處理分開在不同的時間處理

2. 連接 Oracle 資料庫的部分避免使用 Link Server,而改用 SSIS 直接連接。

3. 在 SP 內將原本的利用 # 所建立的暫時性物件,在完成之後要加入刪除該物件和所建立的 INDEX ( 可參考 Caching of Temporary Objects )

4. 將 tempdb 移到其他的磁碟上並按照 CPU 核心數,適當增加多個檔案。

5. 每週資料庫完成維護計畫之後,配合上述釋放記憶體的指令後,重新配置 tempdb 的大小。

 

這樣調整之後,應該就可以比較不容易再發生類似的狀況了。