如何在SQL Server的tempdb上“减肥”

SQL Server将自动创建一个名为tempdb的数据库作为工作区。当您在存储过程中创建临时表(如(CREATE TABLE #MyTemp ))时，无论您使用哪个数据库，SQL数据库引擎都将在tempdb数据库中创建该表。

此外，当您对大型结果集进行排序时，例如使用ORDER BY或GROUP BY或UNION或执行嵌套SELECT，如果数据量超过了系统内存容量，SQL数据库引擎将在tempdb中创建一个工作表。当您运行DBCC重新索引或向现有表中添加聚类序列时，SQL数据库引擎也将使用tempdb。事实上，任何针对大型表的ALTER TABLE命令都会消耗tempdb中的大量磁盘空间。

理想情况下，SQL会在指定操作完成后自动清理并销毁这些临时表，但很多问题会导致错误。例如，如果您的代码创建了一个事务，但未能执行或重新运行它，这些孤立对象将保留在tempdb中。此外，当在大型数据库上运行DBCC检查时，它将消耗大量空间。您经常会发现tempdb比预期的要大得多，甚至会收到一条错误消息，指出SQL即将耗尽磁盘空间。

有许多方法可以纠正这种情况，但从长远来看，您需要执行其他步骤来确保正常使用。

为tempdb“减肥”最简单的方法就是关闭SQL数据库引擎并重启，但在重要任务中，这样做可能会非常困难；另一方面，如果你处于一种无法忍受的状态，那么我的建议是告诉你的老板这个坏消息，然后开始手术。

如果您足够幸运，有另一个磁盘来放置tempdb，您可以执行以下操作:

使用母版

去

ALTER DATABASE tempdb修改文件(name = tempdev，filename = ' new drive:Path empdb . MDF ')

去

ALTER DATABASE tempdb修改文件(name = templog，filename = ' new drive:Path emplog . ldf ')

去

应该检查tempdb的另外三个属性:自动增长标记、初始大小和恢复模式。以下是关于这些属性的一些提示:

自动增长标志:记住将这个标志设置为True。

初始大小:tempdb的初始大小应该根据常见的工作负载来设置。如果许多用户都在使用GROUP BY、ORDER BY或聚合大型表，那么您的公共工作负载将会非常大。如果服务器脱机，您可能需要检查日志文件和数据文件是否位于同一个磁盘上。如果是这样，您应该将它们转移到新的磁盘上。您只需要指出相应的数据库并使用相同的命令。

恢复模式:将恢复模式设置为True意味着SQL将自动截断tempdb的日志文件(在使用每个表之后)。要找出tempdb使用的恢复模式，可以使用以下命令:

SELECT DATABASEPROPERTYEX(' tempdb '，' recovery ')

恢复模式有三个选项:简单、完整或大容量日志。如果要更改设置，可以使用以下命令:

更改数据库临时数据库集恢复简单

这些步骤可以优化系统中使用的tempdb。除了解决磁盘空间的问题，你还会发现SQL Server系统性能的提升。