在SQL Server tempdb滿時檢查資料檔案

作為一名資料庫DBA，肯定會聽說過“tempdb資料庫滿了”。通常我們很容易確定造成這一問題的原因。但是更多的時候這一問題主要源於一組請求，涉及到新程式碼部署或逐漸增加的資料。

　　“Tempdb滿了”意味著什麼？

　　當SQL Server tempdb滿了時，上層管理常常需要決策、一些開發人員可能會推卸責任，就連高階DBA也害怕碰到這種情況。

　　和我告訴管理員的一樣，首先經驗的做法就是：保持冷靜。不要讓還沒有公佈的情況給其他方面造成壓力，那樣可能釀成更大的錯誤。

　　既然情況已經出現了，那我們就來解決問題。Tempdb資料庫由兩部分組成：一是原始檔案組裡的資料檔案，二是tempdb日誌檔案。這兩者都可能出錯，但錯誤資訊會告訴你哪一部分滿了。首先我們一起看看資料檔案部分。在以後的文章部分中再講解日誌檔案。

　　我們怎麼壓縮原始檔？
　
　　首先我們要了解一下確定是什麼佔用大部分空間的方法，哪一個伺服器有我們處理的ID號（SPID）、請求是從哪一臺主機上發出的。以下查詢將返回資料庫裡佔空間的前1000個SPID。記住這些返回的值為頁碼數。為此，我算了一下儲存值（單位為MB）。同樣，我們還要注意計數器是隨著SPID的使用時間而逐漸積累的：

SELECT top 1000     s.host_name, su.[session_id], d.name [DBName], su.[database_id],     su.[user_objects_alloc_page_count] [Usr_Pg_Alloc], su.[user_objects_dealloc_page_count] [Usr_Pg_DeAlloc],     su.[internal_objects_alloc_page_count] [Int_Pg_Alloc], su.[internal_objects_dealloc_page_count] [Int_Pg_DeAlloc],     (su.[user_objects_alloc_page_count]*1.0/128) [Usr_Alloc_MB], (su.[user_objects_dealloc_page_count]*1.0/128)     [Usr_DeAlloc_MB],     (su.[internal_objects_alloc_page_count]*1.0/128) [Int_Alloc_MB], (su.[internal_objects_dealloc_page_count]*1.0/128)     [Int_DeAlloc_MB]     FROM [sys].[dm_db_session_space_usage] su     inner join sys.databases d on su.database_id = d.database_id     inner join sys.dm_exec_sessions s on su.session_id = s.session_id     where (su.user_objects_alloc_page_count > 0 or     su.internal_objects_alloc_page_count > 0)     order by case when su.user_objects_alloc_page_count > su.internal_objects_alloc_page_count then su.user_objects_alloc_page_count else su.internal_objects_alloc_page_count end desc

　　第二個查詢也非常類似，它返回的是SPID給分配空間的前1000條。該查詢能跟蹤可以迴圈、建立專案或執行時建立、刪除多個臨時物件的程式。

SELECT top 1000 s.host_name, su.[session_id], d.name [DBName], su.[database_id],     su.[user_objects_alloc_page_count] [Usr_Pg_Alloc], su.[user_objects_dealloc_page_count] [Usr_Pg_DeAlloc],     su.[internal_objects_alloc_page_count] [Int_Pg_Alloc], su.[internal_objects_dealloc_page_count] [Int_Pg_DeAlloc],     (su.[user_objects_alloc_page_count]*1.0/128) [Usr_Alloc_MB], (su.[user_objects_dealloc_page_count]*1.0/128)     [Usr_DeAlloc_MB],     (su.[internal_objects_alloc_page_count]*1.0/128) [Int_Alloc_MB], (su.[internal_objects_dealloc_page_count]*1.0/128)     [Int_DeAlloc_MB]     FROM [sys].[dm_db_session_space_usage] su     inner join sys.databases d on su.database_id = d.database_id     inner join sys.dm_exec_sessions s on su.session_id = s.session_id     where (su.user_objects_dealloc_page_count > 0 or     su.internal_objects_dealloc_page_count > 0)     order by case when su.user_objects_dealloc_page_count > su.internal_objects_dealloc_page_count then     su.user_objects_dealloc_page_count else su.internal_objects_dealloc_page_count end desc

　　由於tempdb在壓縮後沒有報告它的大小，以下查詢可以提供tempdb裡的有用空間。

SELECT sum(unallocated_extent_page_count) [Free_Pages],     (sum(unallocated_extent_page_count)*1.0/128) [Free_Space_MB]     FROM sys.dm_db_file_space_usage

　　如果你已經決定了SPID，你就可以決定用dbcc緩衝器（SPID）執行什麼樣的T-SQL。

　　假設你清楚執行的T-SQL程式碼，但是你還需要知道會牽涉到的臨時表。你可以執行以下程式：

select * from tempdb.sys.objects where type = 'u'

　　臨時表源於T-SQL裡那些應該有#YourDefinedTblName____UniqueID格式的使用者。它能幫你識別涉及到的程式碼。你還可以用sys.dm_exec_requests命令聯結SPID、用sys.dm_exec_sql_text(SQL_Handle)獲取當時執行的命令，但要求指令碼在實際執行時用“polling loop”監控。

　　小結

　　在現有的系統表和檢視的基礎上，我們很難在沒有預先準備的基礎上解決問題。充滿的tempdb有時可以像單個SPID那麼簡單，有時像一組會話一樣複雜，但是上面我所概述的這些步驟幫你將問題化小。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-594831/，如需轉載，請註明出處，否則將追究法律責任。