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

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

以下是可以安排為自動運行的一些維護任務：

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

通過刪除空數據庫頁壓縮數據文件。

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

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

備份數據庫和事務日志文件。數據庫和日志備份可以保留一段指定時間。這使您可以為備份創建一份歷史記錄 ，以便在需要將數據庫還原到早于上一次數據庫備份的時間的時候使用。還可以執行差異備份。

運行 SQL Server 代理作業。這可以用來創建可執行各種操作的作業以及運行這些作業的維護計劃。 維護任務生成的結果可以作為報表寫入文本文件，或寫入 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 filesDBCC SHRINKDATABASE (N'AdventureWorks', 10, NOTRUNCATE)--選擇Return freed space to operating systemDBCC 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 objectsALTER 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 代理作業歷史記錄和維護計劃歷史記錄進行刪除。此任務封裝 sp_delete_backuphistory 系統存儲過程并將指定日期作為參數傳遞給該過程。 選項：Backup and restore historySql Server Agent job historyMaintenance plan history

生成代碼：declare @dt datetime select @dt = cast(N'2007-05-31T08:00:00' as datetime)exec msdb.dbo.sp_delete_backuphistory @dtGOEXEC msdb.dbo.sp_purge_jobhistory; @oldest_date=@dtGOEXECUTE msdb..sp_maintplan_delete_log null,null,@dtGO

Execute Sql Server Agent Job(執行 SQL Server 代理作業)任務運行 SQL Server 代理作業。SQL Server 代理作業能夠自動執行您需要重復執行的任務。此任務封裝 sp_start_job 系統 過程并把 SQL Server 代理作業的名稱作為參數傳遞給該過程。

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 代理操作員。此任務是唯一一個不封裝 Transact-SQL 語句或 DBCC 命令的數據庫維護任務。

執行維護計劃最好按一定的順序，首先是執行檢查數據庫完整性，然后是收縮數據庫，重新生成索引或者重新組織索引任務，最后是更新統計信息。

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

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