[SQL][問題處理]肥得要死的 tempdb 如何縮減
這幾天有朋友問到，如果 SQL Server 資料庫太大了，要如何縮減 ? 看起來應該不算是個困難的問題，一般來說如果不想去背指令的話，可以利用 SSMS 先挑選欲處理的資料庫，按下滑鼠右鍵選擇「工作」→「壓縮」→「資料庫」
但如果你嫌這樣不夠專業，那麼你想使用指令也是可以的啦 !
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 的大小。
這樣調整之後，應該就可以比較不容易再發生類似的狀況了。