Sql Server 2005 資料庫維護計劃

這個星期開始為了減輕工作壓力開始使用資料庫維護計劃(SQL Server Maintenance Plan Wizard)維護資料庫，由於以前都沒用過，在個人使用的免費版(Express)裡也沒有這個功能，所以現在好好學習了一番，這裡總結一下。

維護計劃嚮導可以用於協助您設定核心維護任務，從而確保資料庫執行良好，做到定期備份資料庫以防系統出現故障，對資料庫實施不一致性檢查。維護計劃嚮導可建立一個或多個 SQL Server Agent作業，代理作業將按照計劃的間隔自動執行這些維護任務。它使您可以執行各種資料庫管理工作，包括備份、運行資料庫完整性檢查、或以指定的間隔更新資料庫統計資訊。建立資料庫維護計劃可以讓SQL Server有效地自動維護資料庫，保持資料庫運行在最佳狀態，並為管理員節省了寶貴的時間。

以下是可以安排為自動啟動並執行一些維護任務：

用新填滿因數重建索引來重新組織資料和索引頁上的資料。這確保了資料庫頁中包含的資料量和可用空間的平均分布，還使得以後能夠更快地增長。

通過刪除空資料庫頁壓縮資料檔案。

更新索引統計資訊，確保查詢最佳化工具含有關於表中資料值分布的最新資訊。這使得查詢最佳化工具能夠更好地確定 訪問資料的最佳方法，因為可以獲得資料庫中儲存資料的詳細資料。雖然 SQL Server 會定期自動更新索引統 計資訊，但是此選項可以對統計資訊立即進行強制更新。

對資料庫內的資料和資料頁執行內部一致性檢查，確保系統或軟體故障沒有損壞資料。

備份資料庫和交易記錄檔。資料庫和記錄備份可以保留一段指定時間。這使您可以為備份建立一份記錄 ，以便在需要將資料庫還原到早於上一次Database Backup的時間的時候使用。還可以執行差異備份。

運行 SQL Server Agent作業。這可以用來建立可執行各種操作的作業以及運行這些作業的維護計劃。

維護任務產生的結果可以作為報表寫入文字檔，或寫入 msdb 中的 sysmaintplan_log 和 sysmaintplan_log_detail 維護計劃表。若要在記錄檔查看器中查看結果，請按右鍵“維護計劃”，再單 擊“查看記錄”。

以下是詳細說明：

Check Database Integrity(檢查資料庫完整性)

任務檢查指定資料庫中所有對象 的分配和結構完整性。此任務可以檢查單個資料庫或多個資料庫，您還可以選擇是否也檢查資料庫索引,檢查所有索引頁以及表資料頁的完整性。

此任務封裝 DBCC CHECKDB 語句。

產生的程式碼：

--檢查當前資料庫，取消資訊性訊息

DBCC CHECKDB WITH NO_INFOMSGS

Shrink Database(收縮資料庫任務)

收縮資料庫’任務”對話方塊可以建立一 個任務，嘗試減小所選資料庫的大小。

此任務封裝了 DBCC SHRINKDATABASE 命令。

選項：

Shrink database when it grows beyond

當資料庫大小超過指定值時收縮資料庫，指定引發此任務的資料庫大小(MB)。

Amount of free space to remain after shrink

收縮後保留的 可用空間，當資料庫檔案中的可用空間達到此值時停止收縮。

Retain freed space in database files

選擇在資料庫檔案中保留所釋放的檔案空間。如果指定 NOTRUNCATE 選項，資料檔案好像沒有收縮。

Return freed space to operating system

選擇把資料檔案中任何未使用空間被釋放給作業系統。無需移動任何資料即可減小檔案大小。

產生的程式碼：

--選擇Retain freed space in database files

DBCC SHRINKDATABASE (N'AdventureWorks', 10, NOTRUNCATE)

--選擇Return freed space to operating system

DBCC SHRINKDATABASE(N'AdventureWorks', 10, TRUNCATEONLY)

Reorganize Index(重新組織索引)

重新組織 SQL Server 資料庫表和視圖中的索引。 通過使用“重新組織索引”任務，包可以重新組織單個資料庫或多個資料庫中的索引。如果此任務僅重新組織單個資料庫中的索引，則可以選擇任務要重新組織其索引的視圖或表。“重新組織索引”任務還包含壓縮大型物件資料的選項。大型物件資料是具有 image 、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 或 xml 資料類型的資料。

此任務封裝了 Transact-SQL ALTER INDEX 語句。

如果選擇壓縮大型物件資料，則該語句使用 REORGANIZE WITH(LOB_COMPACTION = ON) 子句，否則 LOB_COMPACTION 將設定為 OFF。

產生代碼：(只選擇了Employee表)

--選擇compact large objects

ALTER INDEX [PK_Employee_EmployeeID] ON [HumanResources].[Employee] REORGANIZE WITH ( LOB_COMPACTION = ON )

--不選擇

ALTER INDEX [PK_Employee_EmployeeID] ON [HumanResources].[Employee] REORGANIZE WITH ( LOB_COMPACTION = OFF )

Rebuild Index(重建索引)

重建 SQL Server 資料庫表和視圖中的索引。包可 以重建單個資料庫或多個資料庫中的索引。如果任務僅重建單個資料庫中的索引，則可以選擇任務要 重建其索引的視圖和表。使用預設可用空間重新組織頁刪除資料庫中表上的索引，並使用在建立索引時指 定的填滿因數重新建立索引。

此任務封裝 ALTER INDEX REBUILD 語句並提供下列索引重建選項：

Reorganize pages with the default amount of free space

指定 FILLFACTOR 百 分比或使用原始的 FILLFACTOR 量。

Change free space per page percentage to:

索引頁預留空間使用 PAD_INDEX 選項可以在索引建立過程中設定中間級頁中的可用空間百分比。將每頁的可用空間百分比更改,刪除資料庫中表上的索引，並使用新的、自動計算的填滿因數重新建立索引，從而在索引頁上保留指定的可用空間。

Sort results in tempdb

使用 SORT_IN_TEMPDB 選項，該選項確定在索引建立 過程中產生的中間排序結果的臨時儲存位置。使用索引的IGNORE_DUP_KEY 選項，該選項指定對唯一聚集或非叢集索引上多行 INSERT 事務中的重複索引值的錯誤響應 。

Keep index online while reindexing

使用 ONLINE 選項，使用者可以在索引操作期間訪問基礎資料表或叢集索引資料以及任何關聯的非叢集索引。

產生代碼：(只選擇了Employee表)

ALTER INDEX [PK_Employee_EmployeeID] ON [HumanResources]. [Employee] REBUILD WITH ( FILLFACTOR = 90, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )

Updata Statics(更新統計資料)

為指定的表或索引檢視表中的一個或多個統計資訊組（ 集合）更新索引值分布資訊。

此任務封裝 UPDATE STATISTICS 語句。

All existing statistics

如果更新應用於所有統計資訊，則暗示使用 WITH ALL 子句。

Column statistics only

如果更新僅 應用於列，則包含 WITH COLUMN 子句。

Index statistics only

如果更新僅應用於索引，則包含 WITH INDEX 子句。

Full scan

全部統計

Sample by

從每個索引所對應的表中抽樣的資料，此樣本的大小取決 於表中的行數和資料修改的頻率。

產生代碼：(只選擇了Employee表)
UPDATE STATISTICS [HumanResources].[Employee]

WITH FULLSCAN

Clean Up History(清除記錄)

使用“清除記錄”對話方塊，可以放 棄 msdb 資料庫表中舊的曆史資訊。此任務支援對備份與還原記錄、Microsoft SQL Server Agent作業記錄和維護計劃記錄進行刪除。

此任務封裝 sp_delete_backuphistory 系統預存程序並將指定日期作為參數傳遞給該過程。

選項：

Backup and restore history

Sql Server Agent job history

Maintenance plan history

產生代碼：

以下為引用的內容： declare @dt datetime select @dt = cast(N'2007-05-31T08:00:00' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt GO EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date=@dt GO EXECUTE msdb..sp_maintplan_delete_log null,null,@dt GO

Execute Sql Server Agent Job(執行 SQL Server Agent作業)

任務運行 SQL Server Agent作業。SQL Server Agent作業能夠自動執行您需要重複執行的任務。

此任務封裝 sp_start_job 系統 過程並把 SQL Server Agent作業的名稱作為參數傳遞給該過程。

Back Up Database Task

備份用的，太熟悉了，不介紹了。

Maintenance Cleanup Task

此任務封裝 master.dbo.xp_delete_file 系統過程，用來刪除備份檔案。

Execute T-SQL Statement Task

執行T-SQL 任務運行Transact-SQL 陳述式。這個任務用嚮導的時候是沒有的，要到設計檢視裡面去拖出來。

Notify Operator Task

通知操作員任務將通知訊息發送到 SQL Server Agent操作員。此任務是唯一一個不封裝 Transact-SQL 陳述式或 DBCC 命令的資料庫維護任務。

執行維護計劃最好按一定的順序，首先是執行檢查資料庫完整性，然後是收縮資料庫，重建索引或者重新組織索引任務，最後是更新統計資料。

重建索引或者重新組織索引要根據情況選擇不同的操作，兩個一起選擇沒有什麼意義。決定使用哪種磁碟重組方法的第一步是分析索引以確定片段程度。使用系統函數 sys.dm_db_index_physical_stats 可以檢測特定索引、表或索引檢視表的所有索引、一個資料庫中的所有索引或所有資料庫中的所有索引中的片段。知道片段程度後，可以確定修複片段的最佳方法。索引片段不太多時，可以重新組織索引。不過，如果索引片段非常多，重建索引則可以獲得更好的結果。

我們公司這些任務都是一個星期運行一次，幾個資料庫加起來有200G，資料庫也不算很大，每次運行要兩個小時以上，所以都是在淩晨進行。如果進行的是重建索引那麼在執行的時候表是無法訪問的，現在也沒什麼更好的解決方案。這個問題還在繼續學習中！