我的templog.ldf很大(45GB),如果我该怎么办?

我有一个SQL 2005的安装和我的templog.ldf文件不断增长,以消耗它所在的驱动器上的所有可用空间。 有时它会停下来几MB免费,但有时会更进一步,这是我认为这种行为可能牵涉到我已经看到的一些其他问题的c驱动器。

我的问题是,我该怎么做,我可以将日志移动到另一个驱动器,但我有理由认为它不会在那里做同样的事情。 我假设这种行为可能是由于我可以改变的结果,而45GB是tempdb日志不寻常的大小。 我们在代码中使用了很多临时表和表值函数,所以有很多使用tempdb的空间,我可以理解tempdb数据库的增长,但是不了解templog增长的原因。

到目前为止,我已经运行了DBCC OPENTRAN('tempdb')来查看是否有任何旧的事务挂起,而不是。 我已经阅读了关于如何缩小tempdb的问题,并且已经完成了几次,但是我真的很想知道如果我能做些什么来阻止这个事情发生在第一个地方或者更多的细节上,第一个地方。

== == EDITS

1) tempdb正在使用简单的恢复模式

2) templog的增长发生在凌晨几个小时,当时我们有一些计划的查询正在运行,基本上是一个负载的报告,在未来一天的办公时间。 这个文件的大小稳步增长。 我们控制同时运行多less个并发报告,增加并发报告的数量会提高日志的增长速度。

检查您的报告查询。 你有没有DISTINCT在里面? 他们有没有笛卡尔联接?

是否有任何报告查询访问链接服务器作为连接的成员? 如果是这样,可能会导致tempdb日志和数据库增长。

当报告运行在早上时,他们中的任何一个崩溃?

我们遇到了类似的问题,在向微软公司提出PSS电话后,深入调查了这个问题后,我们划分了以下可能的原因和解决scheme。

原因:

导致症状的可能原因是用户数据库所在的磁盘/ LUN存在严重的I / O响应问题; 这会导致用户数据库上的自动检查点花费很长时间才能完成。

现在,tempdb上的检查点只有在tempdb日志变为70%时才会发生,并且它的优先级比用户数据库检查点低。 因此,当用户数据库上的自动检查点发出并正在尝试完成时,由于tempdb用量过大,会导致tempdb日志文件快速填满; 在70%的日志使用情况下,会发生tempdb检查点,但排在用户数据库检查点之后。

在用户数据库检查点完成所需的时间内,tempdb日志文件一直在填充,如果设置了自动增长,则日志文件将在需要更多空间时增长。 这是日志文件不断增长的原因。

总之,您描述的症状的最可能的根本原因是由于您的用户和/或tempdb数据库/日志文件的磁盘/ LUN的I / O响应较差。

解:

我们在解决问题的同时,通过设置一条警报,在tempdb日志文件已满75%时触发警报,并在响应中执行强制执行手动“CHECKPOINT”的作业(优先于自动系统检查点),清除tempdb日志,防止它无限期地自动增长。 将日志文件自动增长以应对其他任何可能性仍然是一个好主意。 此外,我强烈build议您考虑减lesstempdb日志文件的大小,以便在修复之后根据您的环境进行一些有意义的操作。

希望这可以帮助。

什么是在临时数据库上设置的恢复模式? 如果未设置为简单,则将其设置为简单。 这应该防止它增长。 如果它已经设置为简单,那么我会说有一个潜在的问题需要解决,任何缩小文件的尝试只是治疗问题的症状,而不是根本原因。

我花了几个小时阅读和做笔记

http://technet.microsoft.com/en-gb/library/cc966545.aspx

这里有很多细节和解决问题的build议。 看来,除非你的tempdb正在扩展,并且永远不会停止增长,否则它可能只占用它需要的空间量,并且应该已经被初始化为这个大小。 有一节介绍如何估计tempdb所需的空间,以及如何追踪可能占用tempdb空间的内容。 作为这个的结果,我要做的第一件事就是把tempdb移到一个更大的驱动器上,看看那里发生了什么。

有一个标题为“tempdb日志所需的空间”的部分 ,它指出哪些function使用日志,还有另一个前面的部分详细介绍了使用tempdb的function的超集。

“监视I / O”部分有一些关于性能计数器的观点,快速看看我的服务器把这些放在了你可能是一个瓶颈的地方。 我会监视这些一段时间,看看事情如何泛滥。 tempdb日志文件的使用率实际上还不到50%,这符合今天早上在负载下扩展的想法,并保留了这个空间。

我将继续前进,因为它的发展规模就是它所需要的规模,将来要监控这个规模,并确保在任何驱动器上都有增长的空间。 正如在这里的一些build议,我会看看什么是执行临时logging扩展,看看有什么东西可以在那里调整。 我也会关注那些性能计数器,看看是否需要处理。

还有一个额外的有趣的部分标题为“升级到SQL Server 2005” ,它指出在2005年比2000年更多的事情使用tempdb(新function和以前不使用tempdb的现有function)。 我只是最近升级到2005年,所以这可能是突然成为一个问题的原因的一部分。 我不记得在2005年提到升级到2005年的任何地方,这有点痛苦。

这很可能是由失控交叉联接查询造成的。 最好的办法是使用Profiler来查找并修复它。

另一种find它的方法是限制TempDB日志文件的大小,然后等待查看哪些查询失败。 不过,我敢打赌,这已经是失败了,没有人告诉你。

如果您重新启动SQL引擎,文件将被设置为初始大小。 你应该把所有的自动增长文件的最大尺寸。

某些SQL查询或存储过程正在做坏事情 – 唯一可以做的就是对tempdb进行概要分析以捕获“数据库:日志文件自动增长”事件,如果发生这种情况,请使用事件探查器和查询来查找类似sysprocesses的表来查找看看坏的过程是什么。

不幸的是,追溯stream氓程序的老式侦探工作。

您是否尝试过使用DBCC SHRINKFILE()调整tempdb日志文件的大小? 如果是这样,从[非常小的值]到45GB或更多,需要多长时间?