TempDB变大

[faoyy]

如题，之前也才不到1G，突然间增加到了一百多G，第一次重启服务器，有变小，第二次重启的时候就没有降低了，是什么原因，网上查找是说创建了太多了临时表等的，而且没有及时清空掉，请问还有其他原因吗，最近有将数据库的恢复模式从完整改为简单，才开始这样的，跟这个恢复模式有关系吗？

[發糞塗牆]

tempdb本身就是简单模式。你的这种情况我初步怀疑是有什么重启后自动运行的操作或者未关闭的事务占用了tempdb。还有你要注意tempdb的初始大小。另外还有一个“model”数据库，这个库里面的对象再重启之后，也会自动产生到tempdb里面，所以看看model库有没有一些你不希望的对象如表、触发器等存在。

[faoyy]

引用 1 楼 DBA_Huangzj 的回复:

tempdb本身就是简单模式。你的这种情况我初步怀疑是有什么重启后自动运行的操作或者未关闭的事务占用了tempdb。还有你要注意tempdb的初始大小。另外还有一个“model”数据库，这个库里面的对象再重启之后，也会自动产生到tempdb里面，所以看看model库有没有一些你不希望的对象如表、触发器等存在。

我看了下Model数据库，里面都没什么东西的也不大，应该是程序中有什么处理不当的地方导致数据库临时对象没有及时释放吧，但是为什么我重启后数据库和服务器都还是没有降低呢

[xiaoxiangqing]

怎么会这么大，应该是有问题了

[ai_li7758521]

如下有几处用到tempdb的地方： (1)用户建立的临时表.如果能够避免不用,就尽量避免. 如果使用临时表储存大量的数据且频繁访问,考虑添加index以增加查询效率。 (2)Schedule jobs.如DBCC CHECKDB会占用系统较多的资源,较多的使用tempdb.最好在SQL Server loading比较轻的时候做。 (3)Cursors.游标会严重影响性能应当尽量避免使用。 (4)CTE(Common Table Expression).也会在tempdb中执行。 (5)SORT_INT_TEMPDB.建立index时会有此选项。 (6)Index online rebuild。 (7)临时工作表及中间结果集.如JOIN时产生的。 (8)排序的结果。 (9)AFTER and INSTEAD OF triggers。

[faoyy]

另外我更改的恢复模式是更改另外一个在用的数据库，不是Tempdb，我之前是怀疑更改这个也会有影响，那应该是没有影响的是吗？

[發糞塗牆]

重启瞬间就很大（也就是说不是用了很久），那就是有东西默认启动，占据了那部分

[Cloud_Hero]

“第二次重启的时候就没有降低了”这是什么意思？ tempdb每次重启时候都会被重建，如果重启后就直接变成1GB甚至100GB，这是不正常现象。 “将数据库的恢复模式从完整改为简单”，tempdb推荐使用简单恢复模式。 如果tempdb很大，可能是遇到某些复杂的hash join、游标、临时表等等。

[LongRui888]

理论上说，每次SQL Server重启时，不管之前有多大，都会重建的，大小是model数据库的大小， 你说第一次重启，确实变小了，但第二次重启，就还是那么大，这个很不正常。

           
--查询 tempdb中那部分较大
Select      
       'Tempdb' as DB,       
          
       SUM (user_object_reserved_page_count)*8.0 / 1024 as '用户对象保留的大小',       
	   SUM (internal_object_reserved_page_count)*8.0 / 1024 as '内部对象保留的大小',   
	   SUM (version_store_reserved_page_count)*8.0 / 1024  as '版本存储保留的大小',     
	   SUM (unallocated_extent_page_count)*8.0 / 1024 as '未分配的区中包含的大小',     	
	   
	   SUM(mixed_extent_page_count)*8.0 / 1024 as '文件的已分配混合区中:已分配页和未分配大小'  --包含IAM页	  
From sys.dm_db_file_space_usage                                          
Where database_id = 2  


--进一步定位是那些会话，消耗了大量的tempdb空间
select session_id,
       
       DB_NAME(database_id) as dbname,
       
       user_objects_alloc_page_count,   --分配的用户对象的页数
       user_objects_dealloc_page_count, --释放的用户对象的页数
       
       internal_objects_alloc_page_count,  --分配的内部对象的页数
       internal_objects_dealloc_page_count --释放的内部对象的页数
       
from sys.dm_db_session_space_usage
order by user_objects_alloc_page_count + 
         internal_objects_alloc_page_count desc