文章編號: 307487 - 上次校閱: 2011年8月30日 - 版次: 7.0

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

檢視此文章適用的產品。
系統提示本文適用於您使用的作業系統之外的作業系統。與您不相關的文章內容已停用。
本文曾發行於 CHT307487

在此頁中

全部展開 | 全部摺疊

結論

本文將告訴您，可以用來壓縮 Tempdb 資料庫的三種方法，以使其大小小於上次的設定大小。第一種方法能完全控制 Tempdb 的檔案大小，但必須重新啟動 SQL Server。第二種方法會壓縮整個 tempdb 資料庫，但是有幾項限制，其中包括重新啟動 SQL Server。第三種方法則可壓縮 Tempdb 中的個別檔案。後面兩種方法均規定在執行壓縮時，Tempdb 資料庫不得進行任何活動。

注意 這些方法也適用於 SQL Server 2005。但在此情況下，您應該使用 SQL Server Management Studio 來執行這些作業，而不要使用 Enterprise Manager 及 Query Analyzer。請注意，壓縮作業完成後，SQL ServerManagement Studio 2005 無法顯示 tempdb 檔案的正確大小。「目前配置的空間」值一律從 sys.master_files DMV 取出，且這個值在 tempdb 資料庫壓縮作業完成後沒有更新。如果要取得壓縮作業完成後的 tempdb 檔案正確大小，請在 Management Studio 中執行下列陳述式：
使用 tempdb
從 sys.database_files 選取 (大小*8) 作為 FileSizeKB

注意 SQL Server 2008 不會受到此問題影響 (GUI 顯示為大小錯誤。)

回此頁最上方

Tempdb 資訊

tempdb 是一個暫時性工作區。此工作空間有很多用途，SQL Server 使用 Tempdb 做為：
  • 明確建立之暫存資料表的儲存區。
  • 工作資料表，用以儲存在查詢處理及排序期間所建立的中繼結果。
  • 具體化的靜態資料指標。
SQL Server 只在 Tempdb 交易記錄中記錄足夠的資訊，以便在進行資料庫復原時回復交易，而不是重做交易。這項功能可以提升 Tempdb 中的 INSERT 陳述式效能。此外，由於每次重新啟動 SQL Server 時都會重建 Tempdb，所以就不需記錄用來重做交易的資訊；因此並無任何可向前復原或回復的交易。當 SQL Server 啟動時，便會使用模型資料庫的複本來重建 Tempdb，並將其大小重設為上次的設定大小。

根據預設，Tempdb 資料庫被設定為視需要自動成長；因此該資料庫可能逐漸成長到超過所需的大小。您只要重新啟動 SQL Server，就能將 Tempdb 的大小重設為上次的設定大小。所謂的設定大小是指上次透過檔案大小變更作業 (例如使用 ALTER DATABASE 加上 MODIFY FILE 選項，或者使用 DBCC SHRINKFILE 陳述式) 所明確設定的大小。本文介紹的三種方法可以將 Tempdb 壓縮至小於設定大小。


回此頁最上方

壓縮 Tempdb 的第 1 種方法

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

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

    sqlservr -c -f

    -c 和 -f 參數會使 SQL Server 以最基本的設定模式啟動，其中 Tempdb 的資料檔大小為 1 MB，而記錄檔大小為 0.5 MB。

    注意：如果您使用 SQL Server 具名執行個體，則必須變更到適當的資料夾 (Program Files\Microsoft SQL Server\MSSQL$instance name\Binn) 並使用 -s 參數 (-s%instance_name%)。
  2. 使用 Query Analyzer 連接到 SQL Server，然後執行下列 Transact-SQL 命令：
    ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = target_size_in_MB) 
--想要的資料檔案目標大小

ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = target_size_in_MB)
--想要的資料檔案目標大小
  3. 在命令提示字元視窗中按 Ctrl-C 以停止 SQL Server，再將 SQL Server 重新啟動為服務，然後確認 Tempdb.mdf 和 Templog.ldf 檔案的大小。
這種方法的限制是，它只能壓縮預設的 Tempdb 邏輯檔案：Tempdev 和 Templog。如果 Tempdb 加入了其他檔案，您可在 SQL Server 重新啟動為服務之後，壓縮這些額外加入的檔案。Tempdb 的所有檔案會在啟動時重建，因此它們是空的檔案，所以您可以將它們移除。如果要移除 Tempdb 的其他檔案，請使用加上 REMOVE FILE 選項的 ALTER DATABASE 命令。

回此頁最上方

壓縮 Tempdb 的第二種方法

使用 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) 的大小是 100 MB，記錄檔 (Tempdb.ldf) 大小是 30 MB。假設 sp_spaceused 報告顯示主要資料檔中含有 60 MB 的資料，而您要將主要資料檔壓縮為 80 MB。現在，請計算壓縮後所希望的剩餘可用空間百分比，先是 80 MB - 60 MB = 20 MB，然後將 20 MB 除以 80 MB，求出 target_percent = 25%。壓縮資料庫後，交易記錄檔也會隨之壓縮，因此剩餘的可用空間佔有 25% 或 20 MB。
  2. 使用 Query Analyzer 連接到 SQL Server，然後執行下列 Transact-SQL 命令：
    dbcc shrinkdatabase (tempdb，「目標百分比」) 
-- 此命令會壓縮整個 tempdb 資料庫
在 Tempdb 資料庫上執行 DBCC SHRINKDATABASE 命令有一些限制。資料檔和記錄檔的目標大小不得低於資料庫建立時的指定大小，或上次透過檔案大小變更作業 (例如使用 ALTER DATABASE 加上 MODIFY FILE 選項，或者使用 DBCC SHRINKFILE 命令) 所明確設定的大小。DBCC SHRINKDATABASE 的另一項限制是，必須根據目前的已使用空間算出 target_percentage 的參數值。



回此頁最上方

壓縮 Tempdb 的第三種方法

使用 DBCC SHRINKFILE 命令來壓縮 Tempdb 中的個別檔案。DBCC SHRINKFILE 比 DBCC SHRINKDATABASE 更具彈性，因為這個命令可用來壓縮單一資料庫檔案，而且不會影響隸屬於同一個資料庫的其他檔案。DBCC SHRINKFILE 的 target size 參數就是所希望的資料庫檔案最終大小。

重要：當您執行 DBCC SHRINKFILE 命令時，Tempdb 資料庫不得進行任何活動。為了確定 DBCC SHRINKFILE 執行時，其他處理序均無法使用 Tempdb，您必須以單一使用者模式來重新啟動 SQL Server。如需有關 DBCC SHRINKFILE 的詳細資訊，請參閱本文的＜Tempdb 尚在使用時，執行 DBCC SHRINKDATABASE 或 DBCCSHRINKFILE 所造成的影響＞一節。
  1. 決定 Tempdb 的主要資料檔 (Tempdb.mdf)、記錄檔 (Templog.ldf) 及/或其他附加檔案的希望大小。請確定這些檔案的已使用空間小於或等於您所希望的目標大小。
  2. 使用 Query Analyzer 連接到 SQL Server，然後針對需要壓縮的特定資料庫檔案執行下列 Transact-SQL 命令： 
    使用 tempdb
執行

dbcc shrinkfile (tempdev，「以 MB 為單位的目標大小」)
執行
-- 此命令會壓縮主要資料檔

dbcc shrinkfile (templog，「以 MB 為單位的目標大小」)
執行
-- 此命令會壓縮記錄檔，請查看最後一個段落。
DBCC SHRINKFILE 的優點在於，可將檔案壓縮的比檔案的原始大小還小。您可以對任何資料檔或記錄檔發出 DBCC SHRINKFILE，而 DBCC SHRINKFILE 的限制則是，無法將資料庫壓縮的比模型資料庫還小。

SQL Server 7.0 會延遲交易記錄檔的壓縮作業，因此您必須發出記錄檔截斷命令並備份，以協助進行資料庫壓縮作業。但是，根據預設，Tempdb 的 trunc log on chkpt 選項會被設為 ON，所以您就不必對該資料庫發出記錄檔截斷命令。如需有關如何壓縮 SQL Server 7.0 中的資料庫交易記錄檔的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
256650? (http://support.microsoft.com/kb/256650/zh-tw/ ) INF：如何將 SQL Server 交易記錄檔壓縮

回此頁最上方

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. (伺服器：訊息 2501，層級 16，狀態 1，行 1 找不到名為 1525580473 的資料表。請檢查 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. (伺服器：訊息 8909，層級 16，狀態 1，行 0 資料表毀損：物件識別碼 1，索引識別碼 0，頁面識別碼 %S_PGID。首頁中的 PageID = %S_PGID)
雖然錯誤 2501 未必代表 Tempdb 有任何毀損，卻會導致壓縮作業失敗。另一方面，錯誤 8909 指出 Tempdb 資料庫已經毀損。請重新啟動 SQL Server 以重建 Tempdb，進而清除此一致性錯誤。但是請記住，可能還有其他原因也會造成類似錯誤 8909 的實體資料毀損錯誤，包括輸入/輸出子系統發生問題等等。

回此頁最上方

參考資料

《SQL Server 線上叢書》；主題：＜DBCC SHRINKFILE＞、＜DBCC SHRINKDATABASE＞

回此頁最上方
這篇文章中的資訊適用於:
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
回此頁最上方
關鍵字：?
kbsqlsetup kbhowtomaster KB307487
回此頁最上方
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。