在日常运维工作中,有时会出现TempDB数据量剧增的情况,此时可从如下方面进行问题的排查。
一、查看日志
–查看日志大小
dbcc sqlperf(logspace)
–查看日志状态
select name,log_reuse_wait_desc from sys.databases
–查看虚拟日志
dbcc loginfo
若TempDB数据文件过大,可考虑收缩数据文件。
收缩方法请参考《SQL Server TempDB 收缩方法》
二、查看tempdb记录的分配情况
use tempdb
go
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
说明:
session_id :稍等可以查询该session的相关信息
internal_objects_alloc_page_count :分配给session内部对象的数据页
internal_objects_dealloc_page_count :已经释放的数据页
login_name : 该session的登录名
三、查看执行语句
上一步操作,已经查出了引起问题的session,并知道了session_id,现可通过session_id查出执行的sql语句。
select p.*,s.text
from master.dbo.sysprocesses p
cross apply sys.dm_exec_sql_text(p.sql_handle) s
where spid = XXX //使用时将XXX换成实际session_id值