tempdb在SQLServer数据库系统中的重要作用

一、tempdb在SQLServer数据库系统中的重要作用

tempdb是SQLServer数据库的系统，一直是SQL Server的一个重要组成部分，用于存储临时对象。你可以简单的理解为SQLServer TempDB是速记。这个应用程序和数据库可以使用tempdb作为临时数据存储区，一个实例的所有用户共享一个tempdb。很明显这样的设计不是很好。当多个应用程序的数据库部署在同一台服务器，应用程序共享tempdb。如果开发商不注重对tempdb的使用，这些数据库会相互作用，从而影响中的应用。

1、tempdb中的任何数据都不会在系统重新启动后坚持。因为tempdb其实每次SQLServer开始重新创建。这个特征表明tempdb不需要恢复。

2、tempdb总是设置为简单恢复模式，当你想修改它，你会犯错误，事务日志记录已提交的事务被标记为重复使用后，每个检查点。

3、tempdb中只能有一个文件组，并没有更多的文件组可以增加。

4、tempdb用于存储三种类型的对象：用户对象，内部对象，版本存储区

如果SQL Server没有频繁访问tempdb。

tempdb数据库没有影响；相反，如果访问非常频繁，负荷会更糟。

tempdb的表现将对整个数据库的重要影响

它是优化tempdb性能的重要，特别是对大型数据库。

注：优化前tempdb，考虑多少tempdb在SQL Server性能的影响，评估过程中遇到的问题和可行性。

许多在SQL Server活动发生在tempdb中，所以在某些情况下，我们可以减少过度使用许多tempdb提高SQL Server的整体性能。

有几个地方的tempdb的使用方法如下：

（1）用户搭建的临时表。如果你能避免不必要的使用，避免它们。如果使用临时表来存储大量数据和频繁访问，则考虑添加索引以提高查询效率。

（2）安排工作，如DBCC CHECKDB，会占用更多的系统资源，多使用tempdb。最好是当SQL Server加载比较轻的时候。

（3）光标。光标将严重影响性能，应尽可能避免。

（4）CTE（公共表表达式）。它也将执行在tempdb。

（5）该选项时，可以将sort_int_tempdb。为索引设置。

（7）临时工作表和中间结果集，如联接。

（9）触发器之后，而不是触发器。

这是不可能避免的tempdb的使用，如果有tempdb瓶颈或问题，是时候回去考虑这些问题。

在SQL重新启动服务器会自动创建了8MB大小在tempdb，默认自动增加到10%。对于一个小的数据库，8mb大小是足够的。但对于一个庞大的数据库，8mb SQL服务器不能满足频繁活动的需要，它将按10%的比例增加，如1GB，需要很长的时间，这段时间将严重影响SQL Server的SQL Server tempdb性能。建议在初始大小的开始（如下图所示设置为中密度纤维板：300MB，LDF：50MB），也可以通过修改数据库的实现。在SQL Server在tempdb将重新启动时有足够的可用空间，提高效率。

困难是找到一个合理的初始化大小。当SQL Server活动频繁和tempdb不增长，这将是一个适当的值，可以将值设置为初始大小在这个时候。当然，还会有更多的考虑因素。这是一个例子。

三.不收缩tempdb（如果不是必要的）

有时我们会发现tempdb中占据了很大的空间，但在可用的空间相对较低，我们认为收缩数据库释放磁盘空间。在这个时候，我们必须小心，这可能会影响性能。

4。将tempdb文件和其他数据文件的未使用的IO

tempdb有IO要求高。最好分配给一个高IO磁盘，并将磁盘与其他数据文件分开，以提高读写效率。

Tempdb也分为多个文件，通常分为CPU的文件。多个CPU文件分成若干tempdb数据文件。多tempdb文件可以提高阅读和写作的效率和减少IO活动冲突。

选择a.name，金额（大小）* 8/ 1024/ 1024 GB}{

从master.dbo.sysdatabases内加入master.dbo.sysaltfiles B a.dbid= b.dbid

tempdb是SQL Server的一个重要组成部分。今天，数据tempdb是非常大的，所以需要一些理解和总结tempdb的进一步研究。

二、tempdb数据库数据问题

用delete删除表数据的时候，数据库会记录日志，在正式提交前会有日志写入tempdb库，因此该库的数据会不断增加，当提交完成时就会自动释放tempdb空间。

针对你的问题可以考虑以下方法：

1、如果你是表数据全部删除，可使用truncate，使用该命令时可以清除一个表的数据，同时不写入日志，由于不写日志所以删除的速度很快；

2、如果你是该表部分数据删除，可以考虑用delete时将where条件细化，尽量将300W条记录拆分成多个语句来删除，以减少一次性数据删除的数量，从而减少对tempdb的空间占用

另外提醒，不管你是否有把握正确操作，都先备份再删除。祝你成功

三、如何恢复tempdb数据库

tempdb是一个临时工作区。除其他用途外，SQL Server还将 tempdb用于：

•显式创建的临时表的存储。

•保存在查询处理和排序过程中创建的中间结果的工作表。

SQL Server在 tempdb事务日志中记录的信息只足够用于回滚事务，而不足以用于在数据库故障恢复过程中重新执行事务。这一特点提高了 tempdb中 INSERT语句的性能。另外，由于每次重新启动 SQL Server时都会重新创建 tempdb，无需记录用于重新执行任何事务的信息。因此，没有任何要前滚或回滚的事务。当 SQL Server启动时，通过使用 model数据库的副本重新创建 tempdb，并将其重置为上次配置的大小。

默认情况下，tempdb数据库配置为根据需要自动增长；因此，此数据库可能最终增长到大于所需的大小。简单地重新启动 SQL Server会将 tempdb的大小重置为上次配置的大小。配置的大小是用文件大小更改操作（如带有 MODIFY FILE选项的 ALTER DATABASE或者 DBCC SHRINKFILE语句）设置的上次显式大小。本文说明您可以用来将 tempdb收缩到小于其配置的大小的三种方法。

此方法要求您重新启动 SQL Server。

1.停止 SQL Server。打开命令提示符，然后键入以下命令启动 SQL Server：

-c和-f参数使 SQL Server以最小配置模式启动，让数据文件的 tempdb大小为 1 MB，日志文件的 tempdb为 0.5 MB。

注意：如果使用 SQL Server命名实例，必须切换到适当的文件夹(Program Files/Microsoft SQL Server/MSSQL$instance name/Binn)，并使用-s开关(-s%instance_name%)。

2.用查询分析器连接到 SQL Server，然后运行下列 Transact-SQL命令： ALTER DATABASE tempdb MODIFY FILE

(NAME='tempdev', SIZE= target_size_in_MB)

--Desired target size for the data file

ALTER DATABASE tempdb MODIFY FILE

(NAME='templog', SIZE= target_size_in_MB)

--Desired target size for the log file

3.通过在命令提示符窗口中按 Ctrl-C停止 SQL Server，将 SQL Server作为服务重新启动，然后验证 Tempdb.mdf和 Templog.ldf文件的大小。

此方法的局限是它只能对默认的 tempdb逻辑文件 tempdev和 templog进行操作。如果将其他文件添加到了 tempdb，您可以在将 SQL Server作为服务重新启动后收缩它们。在启动过程中将重新创建所有 tempdb文件；因此，它们是空的并可删除。要删除 tempdb中的其他文件，请使用带有 REMOVE FILE选项的 ALTER DATABASE命令。

使用 DBCC SHRINKDATABASE命令将 tempdb数据库作为整体收缩。DBCC SHRINKDATABASE接收参数 target_percent，该参数是数据库收缩后数据库文件中剩余可用空间的所需百分比。如果使用 DBCC SHRINKDATABASE，可能必须重新启动 SQL Server。

重要说明：如果运行 DBCC SHRINKDATABASE，则 tempdb数据库不能正在发生其他活动。要确保在运行 DBCC SHRINKDATABASE时其他进程无法使用tempdb，必须以单用户模式启动 SQL Server。有关更多信息，请参考本文的在使用 Tempdb时执行 DBCC SHRINKDATABASE或 DBCCSHRINKFILE的结果一节。

1.通过使用 sp_spaceused存储过程确定 tempdb中当前使用的空间。然后，计算剩余可用空间的百分比，它将用作 DBCC SHRINKDATABASE的参数；该计算是基于所需数据库大小进行的。

注意：在某些情况下，您可能必须执行 sp_spaceused@updateusage=true来重新计算使用的空间和获得更新的报告。有关 sp_spaceused存储过程的更多信息，请参考 SQL Server联机丛书。

假定 tempdb有两个文件：主数据文件(Tempdb.mdf)和日志文件(Tempdb.ldf)，其大小分别为 100 MB和 30 MB。假定 sp_spaceused报告主数据文件包含 60 MB的数据。还假定您要将主数据文件收缩到 80 MB。计算收缩后剩余可用空间的所需百分比，即 80 MB- 60 MB= 20 MB。现在，用 20 MB除以 80 MB= 25%，这就是您的 target_percent。事务日志文件将据此进行收缩，从而在数据库收缩后剩下 25%即 20 MB的可用空间。

2.用查询分析器连接到 SQL Server，然后运行下列 Transact-SQL命令： dbcc shrinkdatabase(tempdb,'target percent')

-- This command shrinks the tempdb database as a whole

对 tempdb数据库使用 DBCC SHRINKDATABASE命令具有局限性。数据文件和日志文件的目标大小不能小于创建数据库时指定的大小，也不能小于用文件大小更改操作（如带有 MODIFY FILE选项的 ALTER DATABASE命令或 DBCC SHRINKFILE命令）显式设置的上次大小。DBCC SHRINKDATABASE的另一个限制是target_percentage参数的计算和它对当前使用的空间的依赖。