mssq1 sqlserver 查询tempdb数据库所涉及的sql信息


摘要:
tempdb数据库过大,通常是由于创建大量的临时对象,导致tempdb缓存大量数据,
下文将讲述通过系统对象获取tempdb数据库过大所涉及的sql脚本信息,如下所示:

--获取前50条占用 
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  

--根据sessionId获取相应的sql脚本信息
select s.text,p.*
from master.dbo.sysprocesses p   
cross apply sys.dm_exec_sql_text(p.sql_handle) s  
where spid = 63