Sql Server 2005 数据库维护计划

这个星期开始为了减轻工作压力开始使用数据库维护计划(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 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 代理作业历史记录和维护计划历史记录进行删除。
此任务封装 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 代理作业)
任务运行 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，数据库也不算很大，每次运行要两个小时以上，所以都是在凌晨进行。如果进行的是重新生成索引那么在执行的时候表是无法访问的，现在也没什么更好的解决方案。这个问题还在继续学习中！