關於SQLServer的tempdb的資料檔案暴增問題（1）

問題表現：

tempdb的資料檔案暴增，無法收縮。

檢視tempdb用在哪裡？

監視tempdb磁碟空間：

如何確定 tempdb 中的可用空間量，以及如何確定版本儲存區、內部物件和使用者物件使用的空間量。

確定 tempdb 中的可用空間量

下面的查詢將返回 tempdb 中所有檔案的總可用頁數和總可用空間量 (MB)。

SELECT SUM(unallocated_extent_page_count) AS [free pages], 

(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM sys.dm_db_file_space_usage;

確定版本儲存區使用的空間量

下面的查詢將返回 tempdb 中版本儲存區使用的總頁數和總空間量 (MB)。

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],

(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]

FROM sys.dm_db_file_space_usage;

確定執行時間最長的事務

如果版本儲存區使用了 tempdb 中的大量空間，則必須確定執行時間最長的事務。使用下面的查詢可按順序（事務的最長執行時間）列出活動事務。

SELECT transaction_id,session_id

FROM sys.dm_tran_active_snapshot_database_transactions 

ORDER BY elapsed_time_seconds DESC;

與聯機索引操作無關的長時間執行的事務需要很大的版本儲存區。此版本儲存區儲存自事務啟動以來生成的所有版本。聯機索引生成事務可能需要較長時間才能完成，但是使用了專用於聯機索引操作的單獨的版本儲存區。因此，這些操作不會防止刪除其他事務的版本。有關詳細資訊，請參閱行版本控制資源的使用情況。

確定內部物件使用的空間量

下面的查詢將返回 tempdb 中內部物件使用的總頁數和總空間量 (MB)。

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],

(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]

FROM sys.dm_db_file_space_usage;

確定使用者物件使用的空間量

下面的查詢將返回 tempdb 中使用者物件使用的總頁數和總空間量。

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],

(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]

FROM sys.dm_db_file_space_usage;

確定總空間量（可用空間和已用空間）

下面的查詢將返回 tempdb 中所有檔案使用的磁碟空間總量。

SELECT SUM(size)*1.0/128 AS [size in MB]

FROM tempdb.sys.database_files

SQL:

select 

 sm.sysno sosysno

 ,sm.orderdate

 ,replace(replace(replace(replace((ar.ProvinceName + ar.CityName + ar.DistrictName + sm.ReceiveAddress),char(10),''),char(13),''),char(9),''),',','')  address

 ,ar.CityName  city

from master  sm

left join Area  as ar 

 on ar.SysNo = sm.ReceiveAreaSysNo

where 1=1

 and convert(varchar(10),sm.orderdate,111)   between convert(varchar(10),getdate()-1,111)  and convert(varchar(10),getdate()-1,111)

 and sm.ordertype <>5

 and replace(replace(replace(replace((ar.ProvinceName + ar.CityName + ar.DistrictName + sm.ReceiveAddress),char(10),''),char(13),''),char(9),''),',','') is not null

確定問題是行版本控制導致了tempdb的佔用。事務持有未釋放長達522696秒。

抓到那句SQL。殺掉會話。tempdb空間釋放。

SELECT transaction_id,session_id

FROM sys.dm_tran_active_snapshot_database_transactions 

ORDER BY elapsed_time_seconds DESC;

可以進行tempdb的空間收縮了。

PS：查詢當前資料庫tempdb在 使用者物件和 內部物件的使用量：

SELECT 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_time

from 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 >0

or t1.internal_objects_dealloc_page_count>0 

or t1.user_objects_dealloc_page_count>0)

order by t1.internal_objects_alloc_page_count desc

internal_objects_alloc_page_count：內部物件使用量

user_objects_alloc_page_count：使用者物件使用量

internal_objects_dealloc_page_count：內部物件釋放量

user_objects_dealloc_page_count：使用者物件釋放量