Tempdb initial size和dbcc shrinkfile

stswordman發表於2013-10-09

在使用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.

   

更多資訊

===

  1. 需要注意的是，我們這裡對tempdb的initial size的定義，是每次SQL Server重啟後重新建立出來的新的tempdb的初始大小。而不是你在SSMS的資料庫屬性介面中看到的initial size的值。我們認為只有重啟後tempdb的大小對使用者才是有意義的。
  2. dbcc shrinkfile變更initial size的行為是在sql server 2008中引入的。 在sql server 2005裡，dbcc shrinkfile並不會改變initial size，所以即使無法收縮，重啟後tempdb也會變為之前的大小。
  3. 對於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

 

上述討論所的知識點和技巧都是針對tempdb的，請不要將其應用於user database。關於user database的情況我會在後續的文章裡介紹。

相關文章