Tempdb 不能收缩问题

tempdb initial size 和dbcc shrinkfile

在使用sql server时您可能遇到过下面的情况，tempdb的数据文件初始大小为3mb， 随着对tempdb的使用，tempdb文件逐渐变得很大（例如30GB），导致了磁盘空间不足。 此时您需要立刻释放tempdb文件所占用的空间，这时你会有两个选择：

1. 重启SQL Server。大家都知道SQL Server重启时会创建一个新的tempdb。由于tempdb的initial size并不会随着文件大小的增长而增长，重启SQL Server就会创建一个新的3MB的tempdb。

2. 使用DBCC命令收缩tempdb的大小。如果当前tempdb实际被使用到的空间其实不大，只是文件尺寸很大的话，您可能会考虑使用dbcc shrinkfile来收缩tempdb。通常情况下，这种方案是可行。但是您需要警惕一个潜在的风险。下面是我最近遇到的一个实际的案例。

   客户尝试使用dbcc shrinkfile依然无法收缩，但发现tempdb大小没有变化，仍然是30GB。客户搜索到了下面的这篇文章:

Shrink TempDB Transaction Log fails after overflow http://blogs.technet.com/technet_blog_images/b/sql_server_sizing_ha_and_performance_hints/archive/2011/03/03/shrink-tempdb-transaction-log-fails-after-overflow.aspx

http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/04/06/clean-sql-server-cache/

于是尝试使用dbcc freesystemcache('ALL') , 然后使用dbcc shrinkfile，但发现tempdb仍然无法收缩。遇到这样情况可能是因为在使用dbcc freesystemcache('ALL')时，依然有session在引用tempdb，所以无法清除所有的internal objects，导致无法收缩。于是客户决定重启sql server。奇怪的是，SQL Server重启后，客户发现tempdb没有变回初始大小的3mb，而是依旧保持了30GB的尺寸。打开SSMS查看tempdb的初始大小，非常奇怪的，initial size变成了30GB…

为什么会出现这样的结果呢？原因是dbcc shrinkfile会把tempdb的initial size设置为语句运行完之后tempdb文件的当前大小。所以当shrinkfile没能成功收缩tempdb时，tempdb的initial size就变成了30GB，即使重启SQL Server, tempdb依旧还是占用了30GB的空间。

因此在执行dbcc shrinkfile前要千万小心咯。你需要确保没有session引用tempdb的情况下执行dbcc freesystemcache('ALL')， 之后再进行收缩。如果无法保证，那么就只能通过执行alter database，将initial size缩小，然后重启SQL Server来收缩tempdb了。Alter database语句如下：

alter database tempdb modify file(name='tempdev',size=10mb)

请注意，如果指定的size小于当前大小，文件是不会缩小的，但initial size会变成指定的size.

更多信息

需要注意的是，我们这里对tempdb的initial size的定义，是每次SQL Server重启后重新创建出来的新的tempdb的初始大小。而不是你在SSMS的数据库属性界面中看到的initial size的值。我们认为只有重启后tempdb的大小对用户才是有意义的。

dbcc shrinkfile变更initial size的行为是在sql server 2008中引入的。 在sql server 2005里，dbcc shrinkfile并不会改变initial size，所以即使无法收缩，重启后tempdb也会变为之前的大小。
对于tempdb,当有新的数据插入并且现有可用空间无法容纳新的数据时，tempdb文件会按照Autogrowth属性进行增长，但是initial size并不会发生变化。那么initial size这个值到底存储在哪里呢？很遗憾，我在BOL中没有找到。 我尝试在打开SSMS展示initial size时捕获SQL Server trace, 但并没有找到实际表initial size的列和语句。但从实际效果来看，我找到了相应的替代品：
DMV sys.master_files的size列表示initial size。select size,* from sys.master_files where database_id=2

DMV sys.database_files的size表示当前大小。select size ,*From tempdb.sys.database_files

1. SQL Server 2012的SSMS显示的tempdb的initial size和SQL Server重启后重新生成的tempdb的初始大小可能是不同的。而SQL Server 2005/2008/2008 R2的SSMS没有这个问题。根据第一点中我们对initial size的定义，建议如果要查看sql server 2012 tempdb的initial size，请使用DMVsys.master_files
2. Alter database modify file语句改变tempdb的initial size的行为也是在sql server 2008（以及以后版本）中引入的。在2005中无法改变tempdb的initial size.
3. Alter database modify file语句只可以改变tempdb的initial size，不会改变user database的initial size。当尝试更改user database时，如果指定的size小于当前size，会抛出下面的错误：MODIFY FILE failed. Specified size is less than current size.
4. dbcc shrinkfile (Transact-SQL) http://technet.microsoft.com/en-us/library/ms189493(v=sql.90).aspx
5. dbcc shrinkfile (Transact-SQL) http://technet.microsoft.com/en-us/library/ms189493(v=sql.100).aspx
6. How to shrink the tempdb database in SQLServer http://support.microsoft.com/kb/307487

转自 https://www.cnblogs.com/stswordman/p/3358496.html