tempdb专题

查看tempdb的数据文件和日志文件

select name,physical_name from sys.master_files where database_id=db_id('tempdb')

看tempdb log的使用率

dbcc sqlperf(logspace)

看日志状态

SELECT name,log_reuse_wait_desc FROM sys.databases where name='tempdb'

(日志状态是ACTIVE_TRANSACTION时无法收缩日志。NOTHING时可以开始收缩。)

查看哪个会话占用日志

DBCC OPENTRAN
GO
SELECT  *
FROM    sys.dm_exec_sessions AS t2 ,
        sys.dm_exec_connections AS t1
        CROSS APPLY sys.dm_exec_sql_text(t1.most_recent_sql_handle) AS st
WHERE   t1.session_id = t2.session_id
        AND t1.session_id > 50

查看216进程信息：

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

能kill的话，直接杀除会话

kill 216

tempdb收缩日志文件：

DBCC SHRINKFILE (N'templog' , 10)

tempdb收缩数据文件：

DBCC FREESYSTEMCACHE('ALL')
DBCC SHRINKFILE(N'tempdev',100)
DBCC SHRINKFILE(N'temp2',100)