TempDB 收缩方法

1.重启服务，tempdb自动重建

2.执行脚本，避免服务重启
USE [tempdb]
GO
dbcc  freesystemcache('ALL')
Go
DBCC SHRINKfile(N'tempdev' ,10240) --收缩到2MB 
GO

-----------------------------------

3.

--1.清空县志
DUMP TRANSACTION tempdb WITH NO_LOG
--2.截断事务日志:
BACKUP LOG tempdb WITH NO_LOG
--3.收缩业话库大件
dbcc shrinkfile(templog,emptyfile);

查占用大的语句

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  

select s.text,p.*
from master.dbo.sysprocesses p   
cross apply sys.dm_exec_sql_text(p.sql_handle) s  
where spid = 61