SQL Server中遇到tempdb突然暴涨怎么办？
共 2690字，需浏览 6分钟
·
2021-02-05 09:44
点击关注上方“SQL数据库开发”，
设为“置顶或星标”，第一时间送达干货
作者：Huang-ZC
来源：https://blog.csdn.net/kk185800961
今天操作着服务器，突然右下角提示“C盘空间不足”！
吓一跳！~
看看C盘，还有7M！！！这么大的C盘空间怎么会没了呢?搞不好等下服务器会动不了!
第一反应就想可能是日志问题,很可能是数据库日志问题
于是查看日志，都不大，正常。
dbcc sqlperf(logspace)看看系统报错：
C盘已用空间
系统提示
事件结果
事件原因
是tempdb问题，但是刚才看日志才几M，根据提示查看日志状态：
select name,log_reuse_wait_desc from sys.databases
查看系统数据库日志状态
数据库日记现在没什么操作，可能是执行完了。
活动的虚拟日志也不多，10个左右：
dbcc loginfo查看当前tempdb情况，吓一跳啊，tempdb数据文件55G！看上面的图，也就是突然增长的。
于是马上收缩日志，收缩数据文件，收缩出1G左右。
还是不行，继续不断地更改大小不断收缩，只要小于55G都改数据进行收缩，竟然还能收缩了9G！
DBCC SHRINKFILE (N'tempdev' , 1024)--单位为MB
DBCC SHRINKDATABASE (tempdb, 1024);--单位为MB
暂时缓解了，看来是收缩不了了。都说得重启服务器才行，当前连接较多，没有重启.所以先查查什么原因引起的。
查看当前的各种游标，SQL ，堵塞等，没发现什么，事务应该执行完了。
查看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的登录名
从internal_objects_alloc_page_count 和internal_objects_dealloc_page_count可以看出，给session分配了7236696页，计算一下：
select 7236696*8/1024/1024 as [size_GB]竟然为55G，几乎和tempdb增长的大小一致，可以断定就是这个session引起的。internal_objects_dealloc_page_count 可以看到已经释放了，暂用tempdb的数据已经释放了。
通过登录名，已经知道谁在操作了。（这就是给每个相关人员自己登录名的好处之一，可以很快追踪使用者，是内部人员操作）
如果查询上面的DMV距事件发生的时间太久，可能就查不到了。（我这不到5小时再查询，上面的session就查不到了，所以要尽快查看）
现在看看这session_id的用处：
select p.*,s.text
from master.dbo.sysprocesses p
cross apply sys.dm_exec_sql_text(p.sql_handle) s
where spid = 1589
看到最后有一条语句：
拷贝出来，几乎是数据库中最大的 8个表做inner join 连接 查询！！
代码就不贴出来了。
目前已经查出什么原因导致了tempdb增大的问题。tempdb从55285MB收缩为47765MB。
后来因升级重启过服务器，SQLserver服务页就重新启动了，顺便把tempdb的数据文件大小改了。
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 524288KB )
GO
我是岳哥，最后给大家分享我写的SQL两件套：《SQL基础知识第二版》和《SQL高级知识第二版》的PDF电子版。里面有各个语法的解释、大量的实例讲解和批注等等，非常通俗易懂，方便大家跟着一起来实操。
有需要的读者可以下载学习，在下面的公众号「数据前线」(非本号)后台回复关键字：SQL，就行
数据前线
——End——
后台回复关键字：1024，获取一份精心整理的技术干货
后台回复关键字：进群，带你进入高手如云的交流群。
推荐阅读
