tempdb 日志文件增长的问题

     前两天在一个客户那里发现tempdb log 文件增长很大,已经使用40GB了，而tempdb log 文件总的分配空间是70GB，并且日志空间貌似不能重用，他们使用sql 2012 打的sp4补丁，远程分析问题，没有发现长时间开启的事物，业务使用事物都是使用完即时关闭的，而且通过查询tempdb log 文件大小发现

--日志文件使用情况
SELECT s.name,
convert(float,s.size) * CONVERT(float,8) / 1024.0 AS [初始大小MB],
convert(float,i.size) * (8192.0/1024.0)/1024.0 as [分配空间MB],
CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) / 1024.0 AS [使用空间MB]
FROM
sys.master_files AS s ,dbo.sysfiles AS i
WHERE s.name=i.name AND
(s.type = 1 and s.database_id = db_id())
AND s.name = 'templog'

     每隔一两秒的样子，日志文件会增加 3~10 MB的使用空间,因为客户使用的大量的临时表，这个导致tempdb 的log 文件增长，奇怪的是为什么 之前的脏页不会被重用，而是要重新分配新的空间？ 唯一能解释的是可能有 事物长时间开着导致，但前面我们说过通过跟踪查询，没有发现有长时间的事物开启，所以有事务导致tempdb  log 文件持续增加 ，这个原因 就不成立啦；

     通过查阅资料发现，在简单恢复模式下 ，当日志已满 70% 数据库引擎会自动触发检查点， 而我们上面所说客户日志分配空间是 70GB ,已经使用了 40GB,所以还不满足触发检查点刷新脏页的条件；而另一种自动触发自动检查点的机制（恢复间隔）， 则不适用于tempdb ，为了验证这观点，我做了实验  如下：

 

 在本地设置tempdb log的初始大小 109MB,然后创建一张临时表，再写入数据，看看日志文件的使用情况，

 在创建临时表前 先查询下 日志文件的使用情况，使用空间 3.3MB

 

 创建临时表 ，再插入一万条数据

 

 再次查看 tempdb 日志文件的使用情况

 

这时我们可以 继续插入 数据，但不要超过日志70%的空间  ，这时再次记录下使用空间，因为sqlserver 2012 的默认的检查点时间为3分钟 ，我们等3分钟再看 ，会不会自动触发检查点刷新脏页，把日志使用空间回收，

 

 

 三分钟后 再查，日志使用空间依旧没有任何变化

 

 

 如果再插入一万条数据 大概会有 十几MB 的增长，而这时就已经达到 日志70% 使用的条件，会触发检查点去执行，

我们执行下看看 ，#dddd表中已经有8万条数据了

 

 

 

 

 而日志的使用空间，已经因检查点的执行 ，变回为8.5MB的使用空间了。

 所以经过本次测试证实之前的观点是对的，那么我后来把客户的 70GB tempdb log 给设小了 ，设成8GB ,观察了一天，日志使用空间始终在6GB以下。