如何收縮 SQL Server 中的 Tempdb 資料庫

From : http://support.microsoft.com/kb/307487/zh-cn

本文討論將 tempdb 資料庫收縮為小於其上次配置的大小的三種方法。第一種方法使您可以完全控制 tempdb 檔案的大小，但它要求您重新啟動 SQL Server。第二種方法將 tempdb 作為整體來收縮，但它具有某些限制，可能包括重新啟動 SQL Server。第三種方法允許您收縮 tempdb 中的單個檔案。最後兩種方法要求在收縮操作過程中在 tempdb 資料庫中不發生任何活動。

注意：如果您使用的是 SQL Server 2005，這些方法仍適用。不過，您應該使用 SQL Server Management Studio 而不是企業管理器和查詢分析器來執行這些操作。

 

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 收縮到小於其配置的大小的三種方法。

收縮 Tempdb 的方法 1

此方法要求您重新啟動 SQL Server。

1. 停止 SQL Server。開啟命令提示字元，然後鍵入以下命令啟動 SQL Server：

   sqlservr -c -f 

   -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 命令。 

Demo :

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 8) 　　--指定為8M
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = 1)　　--指定為1M
ALTER DATABASE tempdb REMOVE FILE tempdev1　　　　　　　　　　　　--同時刪除tempdev1檔案
SELECT * FROM sysaltfiles s　　　　　　　　　　　　　　　　　　　　　　　　--查看ms sql 2000中各資料庫檔案

 

收縮 Tempdb 的方法 2

使用 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 參數的計算和它對當前使用的空間的依賴。 

收縮 Tempdb 的方法 3

使用命令 DBCC SHRINKFILE 收縮單個 tempdb 檔案。DBCC SHRINKFILE 比 DBCC SHRINKDATABASE 提供更多的靈活性，因為您可以對單個資料庫檔案使用它而不必影響屬於同一資料庫的其他檔案。DBCC SHRINKFILE 接收 target size 參數，這是所需的資料庫檔案的最終大小。 

重要說明：必須在 tempdb 資料庫不發生任何活動時運行 DBCC SHRINKFILE 命令。要確保在執行 DBCC SHRINKFILE 時其他進程不能使用 tempdb，必須以單一使用者模式重新啟動 SQL Server。有關 DBCC SHRINKFILE 的更多資訊，請參見本文中在使用 Tempdb 時執行 DBCC SHRINKDATABASE 或 DBCCSHRINKFILE 的結果 一節。

1. 確定主要資料檔案 (tempdb.mdf)、記錄檔 (templog.ldf) 和/或添加到 tempdb 的其他檔案的所需大小。確保在這些檔案中使用的空間小於或等於所需的目標大小。
2. 用查詢分析器串連到 SQL Server，然後為需要收縮的特定資料庫運行下列 Transact-SQL 命令：

      use tempdb   go   dbcc shrinkfile (tempdev, 'target size in MB')   go   -- this command shrinks the primary data file   dbcc shrinkfile (templog, 'target size in MB')   go   -- this command shrinks the log file, look at the last paragraph.

DBCC SHRINKFILE 的一個優點是它可以將檔案大小減小到小於其原始大小。您可以對任何資料檔案或記錄檔執行 DBCC SHRINKFILE。DBCC SHRINKFILE 的一個局限是您無法使資料庫小於 model 資料庫的大小。 

在 SQL Server 7.0 中，交易記錄收縮是一個延遲操作，您必須執行日誌截斷和備份，以協助進行資料庫中的收縮操作。但是，預設情況下，tempdb 將 trunc log on chkpt 選項設定為“開啟”(ON)；這樣，您就無需為該資料庫執行日誌截斷。有關如何在 SQL Server 7.0 中收縮資料庫交易記錄的其他資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

256650 INF：如何收縮 SQL Server 7.0 交易記錄

 

在使用 Tempdb 時執行 DBCC SHRINKDATABASE 或 DBCCSHRINKFILE 的結果

當正在使用 tempdb 時，如果您嘗試通過使用 DBCC SHRINKDATABASE 或 DBCC SHRINKFILE 命令收縮它，可能會收到與以下類型相似的多個一致性錯誤，並且收縮操作可能失敗：

Server:Msg 2501, Level 16, State 1, Line 1 Could not find table named '1525580473'.Check sysobjects.- 或 -Server:Msg 8909, Level 16, State 1, Line 0 Table Corrupt:Object ID 1, index ID 0, page ID %S_PGID.The PageId in the page header = %S_PGID.

儘管錯誤 2501 可能不表示 tempdb 中的任何損壞，但它會導致收縮操作失敗。與其不同，錯誤 8909 可能表示 tempdb 資料庫中的損壞。應重新啟動 SQL Server 來重新建立 tempdb 並清除一致性錯誤。但是，請記住像錯誤 8909 這樣的物理資料損毀可能有其他原因，這包括輸入/輸出子系統問題。 

參考

SQL Server 聯機叢書；主題：“DBCC SHRINKFILE”；“DBCC SHRINKDATABASE”