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/

https://dba.stackexchange.com/questions/7917/how-to-determine-used-free-space-within-sql-database-files

SQL Server的tempdb文件异常增长的处理

推荐阅读