SQL Server的tempdb文件异常增长的处理
先分析tempdb文件空间各个数值（总的、已用的、空闲的）
USE [tempdb]
GO
SELECT
[TYPE] = A.TYPE_DESC
,[FILE_Name] = A.name
,[FILEGROUP_NAME] = fg.name
,[File_Location] = A.PHYSICAL_NAME
,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
+ CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
+ CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
order by A.TYPE desc, A.NAME; 确定用户对象使用的空间量
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;确定内部对象使用的空间量
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;确定版本存储区（version store）使用的空间量
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
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
通常这是某个数据库启用了读写分离(read committed snapshot)，请参考我的文章https://blog.csdn.net/omage/article/details/89054114
根据上面返回的transaction_id, 可以通过下面的语句查询出session_id
select session_id from sys.dm_tran_session_transactions where transaction_id = [transcation_id]根据返回的会话id查下最近的sql handle
select most_recent_sql_handle from sys.dm_exec_connections where session_id = [session_id]根据sql handle查下sql text
select * from sys.dm_exec_sql_text([sql handle])
根据查询到的sql 语句信息进行分析和优化
为了方便快速查询，笔者将上述步骤合成一个查询
use tempdb
go
select c.client_net_address,c.connect_time,
d.name,s.login_name,s.login_time ,
te.text from sys.dm_exec_connections c inner join
(
select session_id,t.transaction_descriptor,s.elapsed_time_seconds,
t.open_transaction_count from sys.dm_tran_session_transactions t
inner join (
SELECT top 5 transaction_id,elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC
) s on t.transaction_id = s.transaction_id
) as tr on c.session_id = tr.session_id
cross apply sys.dm_exec_sql_text(most_recent_sql_handle) as te
left outer join sys.dm_exec_sessions s on c.session_id = s.session_id
left outer join sys.databases d on s.database_id = d.database_id
参考：
https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms176029(v=sql.105)
https://www.mssqltips.com/sqlservertip/4356/track-sql-server-tempdb-space-usage/
