SQL2005专题站

　　1.关于SQL Server 2005数据维护计划

　　为了使SQL Server数据库的性能保持在最佳的状态，数据库管理员应该对每一个数据库进行定期的常规维护。这些常规任务包括重建数据库索引、检查数据库完整性，更新索引统计信息，数据库内部一致性检查和备份等。这些常规的数据库维护任务需要经常重复，而且繁琐耗时，所以往往被管理员忽略。而且，现在的数据库管理员一天到晚都被很多其他的任务压得喘不过气来，根本没有时间去进行日常维护工作。认识到这些问题的存在，SQL Server通过制定维护计划，提供了一个可以自动或手动执行这些日常维护事务的方法。当确定并创建了维护任务后，日常维护就会根据设定的时间段启动，最终会为企业提供更优质更稳定更值得信赖的数据库。

　　2.SQL Server Service Pack 2数据维护方面的新特性

　　SQL Server Service Pack 2有许多改进的新功能和修复设置已经能够支持维护计划的创建功能。其中改进的特性包括：

　　维护计划设计器支持在一个维护计划里设置多个子计划，而且每个子计划可以具有创建独立任务计划书的功能。多重计划书是备受期待的特性，能够为不同的日常维护事务设置独立的计划表，例如备份、更新统计信息和执行SQL Server作业等。

　　在SQL Server 2005推出的初期，如果企业想要运行维护计划，需要安装SQL Server集成服务(SQL Server Integration Services，SSIS)。不过现在维护计划已经作为一项完全支持的特性整合到了数据库引擎中，所以不再需要启动集成服务了。

　　支持多服务器管理环境，并把维护计划信息记录到远程服务器，以适应不断增加的管理维护计划。可以从一台中央主服务器为所有的目标服务器设置维护计划。

　　最早出现在SQL Server 2000备受欢迎的“清除维护任务”(Maintenance Cleanup Task)重新回到了维护计划里。这个任务可以删除维护计划执行以后任何残留下来的文件。

　　下面列举几个人们预想不到的修复设置，用以改善相关的具体任务：

　　SQL Server 2005 Service Pack 2为数据库备份维护计划任务增加了新的备份过期选项。如果您想让备份设置在某个特定日期之后失效，就可以通过设置备份过期选项来实现。SQL Server 2000具有这个特性，不过在SQL Server 2005发布之初被删除了。

　　您可以另外指定备份文件夹的位置，数据库备份维护计划任务不会再重新设置这个选项为默认位置。

　　过去当您运行备份数据库维护计划任务时，系统可能会错认为您要利用简单恢复模式为系统数据库创建差异和事务日志备份。现在这个缺陷已经修复了。

　　历史清除维护计划任务能够将删除文件的时间选项设置成以小时为单位，大大减少了人工操作时间。

　　更新统计信息任务提供原先在SQL Server 2000维护计划中包含的完全扫描或根据样本大小扫描的选项。

　　3. SQL Server维护计划的任务

　　一个维护计划可以在设定的时间段里运行全套的SQL Server维护任务，以确保数据库引擎里的关系数据库能够优化运行、执行日常备份和检查异常数据。作为SQL Server数据库引擎的一个特性，可以自动创建数据库维护计划并为这些日常维护设置计划书。一个全面的维护计划包括一下几个主要的任务：

• 　　检查数据库完整性
• 　　更新数据库统计信息
• 　　重新组织数据库索引
• 　　进行数据库备份
• 　　清洗数据库历史操作数据
• 　　收缩数据库
• 　　清除维护计划残留文件
• 　　执行SQL Server作业
• 　　清除维护任务

　　注意，和SQL Server 2000不同，日志传送不再包括在维护计划的范畴里。可以在SQL Server Management Studio的数据库水平上或者通过TSQL脚本设置日志传送任务。

　　3.1 检查数据库完整性任务

　　检查数据库完整性任务(Check Database Integrity Task)检验选定的关系数据库中用户和系统表的性能和结构完整性，同时也可以选择检查所有索引页的完整性，检查对象可以是所有的系统和用户数据库，也可以是单个指定数据库。通过维护计划向导(Maintenance Plan Wizard)或使用TSQL语句能够手动创建该任务。

　　下面的语法虽然简单，但提供了在AdventureWorks数据库中创建检查数据库完整性任务所需要的所有信息。

　　3.2收缩数据库任务

　　收缩数据库任务可以把数据库的物理空间和日志文件所占的空间减小到特定值，类似于SSMS中使用的自动收缩任务(Automatic Shrink Task)。收缩对象可以是所有数据库、所有系统数据库、所有用户数据库或单个任务中指定的数据库。该任务会根据您输入的百分比值消除多余的空间。此外，还可以设定各种表示大小(MB)的阈值，包括当数据库大小达到某特定值时的收缩量以及收缩后必须保留的可用空间大小等。可用空间可以保留在数据库里，也可以释放到操作系统中。

　　以下的TSQL语法可以用来收缩AdventureWorks 数据库，并把所释放的空间返回操作系统，且允许在收缩后保留15%的可用空间。

　　但是，如果您要创建维护计划，最好不要选择收缩数据库的选项。首先，数据库收缩操作总是反向进行的，即从文件末端开始释放空间，把分配页移动到文件起始端的未分配页。由于所有的转移操作都会被记录到日志中，所以这个过程会增加事务日志文件的大小。其次，如果数据库的使用频率很高就会产生插入碎片，数据库文件又会不断增加。SQL Server 2005启用即时文件初始化来解决数据库自动增长缓慢的问题，因此增长过程会比过去快。不过，有时候会出现自动增长需要的空间不足的情况，这将造成数据库性能衰退。最后，数据库收缩和增长过于频繁会产生很多文件碎片。如果您想要收缩数据库空间，最好在数据库运行非高峰时段手动进行。

　　3.3 重新组织索引任务

　　重新组织索引任务(Reorganize Index Task)可以整理索引碎片，并压缩与所有表和视图相关联的或者与特定表和视图关联的聚集和非聚集索引，以此来来改善索引扫描性能。受此任务影响的数据库可以是所有的数据库、所有系统数据库、所有用户数据库或单个目标数据库。任务设置了可以用来选择压缩图像或文本等大型对象(LOB)数据的额外选项。

　　为了更深入了解这个任务，下面举一个用来重新组织与AdventureWorks 数据库中的[Sales]. [SalesOrderDetail]表关联的索引的TSQL语法实例，本例中还包含了压缩大型对象数据的选项：

　　3.4 重新生成索引任务

　　重新生成索引任务(Rebuild Index Task)旨在通过重新组织数据库中所有的表索引而清除碎片。此任务对于确保查询性能和应用程序响应不会退化非常有用。因此，当需要对SQL执行索引扫描和查找的时候，系统运行会非常顺畅。另外，此任务能够优化数据和可用空间的再索引页的分配，使数据库增长更加快速。

　　对于可用空间，重新生成索引任务包含以下两个选项：

　　采用默认可用空间大小来重新组织索引页——删除数据库里的表索引，并重新生成索引，生成索引的同时就指定填充因子(fill factor)的值。

　　改变每个索引页的可用空间比例——删除数据库里的表索引，并指定一个自动计算得到的新填充因子值来重新生成索引，因此能够保留索引页上指定的有用空间大小。填充因子的有效值范围从0到100，数值越大，索引页上保留的有用空间就越多，索引就可以增长得越大。

　　重新生成索引的高级选项包括：

　　指定是否在tempdb中存储排序结果——这是重新生成索引的第一个高级选项，相当于索引中的SORT_IN_TEMPDB选项，如果激活这个选项，那么中间排序结果将会在重新生成索引的过程中存储到tempdb中。

　　指定重新生成索引操作中是否保持索引联机——如果设置值为ON，那么这个选项允许用户在重新生成索引操作过程中对基础表、聚集索引数据和相关联的索引进行查询和数据修改操作。

　　为了更深入了解这个任务，下面举一个TSQL语法实例用来重新生成与AdventureWorks 数据库中的[Sales]. [SalesOrderDetail]表关联的索引，例子中采用默认可用空间大小选项，同时将排序结果存储在tempdb中，并在操作过程中保持索引联机：

　　3.5 更新统计信息任务

　　更新统计信息任务(pdate Statistics Task)通过对用户表创建的每个索引统计信息分布进行重新抽样，以确保在一个或多个SQL Server数据库内表和索引中的数据都是最新的。

　　此任务的选项有很多，下面为您一一介绍：

　　数据库——首先选择受此任务影响的数据库。这个选项范围包括所有数据库、所有系统数据库、所有用户数据库或指定数据库。

　　对象——选择完数据库后，就该在对象框中选择限定显示表、显示视图还是两者同时显示。

　　选择——选择受此任务影响的表或索引。如果在对象框中选择了同时显示表和视图选项的话，此选项不可用。

　　更新——“更新”框提供了三个选项。如果需要更新列和索引的统计信息那就选择全部现有统计信息，如果只需要更新列统计信息那就选择仅限列统计信息，如果只更新索引统计信息那就选择仅限索引统计信息。

　　扫描类型——此选项使用户可以对收集已更新统计信息进行完全扫描或通过在抽样选项键入特定值进行扫描。抽样选项的值可以是要抽样的表或索引视图的百分比，也可以是指定的行数。

　　下面是用来更新AdventureWorks 数据库中的[Sales]. [SalesOrderDetail]表的索引统计信息的TSQL语法，例子中选择更新全部现有信息，并执行完全扫描：

　　3.6 清除历史记录任务

　　清除历史记录任务(History Cleanup Task)用几个简单的步骤就可以完全清除数据库表中旧的历史信息。任务支持删除多种类型的数据。下面介绍与此任务相关的几个选项：

　　即将删除的历史数据——使用维护计划向导来清除备份和还原历史记录，SQL Server代理作业历史记录和维护计划历史记录。

　　移除历史数据，如果其保留时间超过——同样是通过维护计划向导实现，用于指定需要删除的数据所保留的最早日期。例如您可以选择以天数、周数、月数或年数为单位作为间隔周期来删除旧数据，系统将自动将该间隔单位转换为日期。

　　当清除历史记录任务完成后，点击“下一步”，调用“选择报告选项”界面，激活检查框中的将报告写入文本文档选项，然后选择保存路径就可以选择将结果报告保存到一个文本文档或用电子邮件发送这份报告给操作人员。

　　下面的TSQL实例显示如何清除保留了超过四星期的备份和还原历史、SQL Server代理作业历史以及维护计划历史等数据：

　　3.7 执行SQL Server代理作业任务

　　执行SQL Server代理作业任务(Execute SQL Server Agent Job task)可以让您把运行已有的SQL Server代理作业和SSIS程序包作为维护计划的一部分。通过在“定义执行SQL Server代理作业任务”界面的可用SQL Server代理作业选项卡选择完成这项任务。同样，也可以通过TSQL语法来通过输入与已有的作业相应的作业ID来执行这项任务。

　　执行此任务的语法如下：

　　EXEC msdb.dbo.sp_start_job @job_id=N'35eca119-28a6-4a29-994b-0680ce73f1f3'

　　3.8 备份数据库任务

　　备份数据库任务(Back Up Database Task)是自动和按计划执行完全备份、差异备份和事务日志备份的最佳途径。

　　当在备份计划中创建以上备份任务时，会有一个包含多个选项的扩张选项集。通过设置这些扩张选项，您可以选择备份一个数据库或单个的组件，设置备份过期时间，验证备份完整性，设置还可以选择是否使用硬盘或磁带备份。下面将对这些备份选项进行详细介绍：

　　指定备份数据库——指定受此任务影响的数据库，下拉菜单提供以下选项：所有数据库、所有系统数据库、所有用户数据库和以下数据库。

　　备份组件——提供备份整个数据库和指定文件和文件组选项。

　　备份集过期时间——只需要输入特定的天数或者输入某个具体日期(如2007年12月30日)，即可指定备份集过期并可被其他备份集覆盖的时间，。

　　备份到——此选项可用于指定将数据库备份到一个文件或磁带，只有系统中存在磁带设备时才可以将数据库备份到磁带，否则可选择备份到网络共享的文件中。

　　跨单个或多个文件备份数据库——单击添加可打开选择备份目标对话框，选择添加或去除一个或多个磁盘或磁带路径。此外，您可以查看文件内容，如果备份文件已存在，可选择追加把备份添加到已存在的备份文件当中。

　　为每个数据库创建备份文件——可以跳过上述的跨单个或多个文件备份数据库选项，选择让SQL Sever自动为每个已选择的数据库创建备份文件。此外，还可以为每个已选择数据库创建一个子目录。注意，如果选择自动创建子目录选项，此子目录将会沿袭上级目录的权限。应当限制相关的NTFS权限，以保护根目录防止未经授权访问。

　　验证备份完整性——当备份操作执行完毕，可使用TSQL语句检查备份是否成功，以及所有卷是否都可读。

　　当您创建维护计划时，可以通过以下三个途径中的任意一个来备份数据库。当需要获得整个数据库时，选择指定备份数据库选项卡中的完全备份;如果只需要记录自最近一次完全备份以来所改变的数据，请选择差异备份;如果只想要备份日志中的访问记录时，请选择事务日志备份。执行完全备份和差异备份任务的备份文件扩展名为.bak，而事务日志备份的为.trn。除了这些不同之处以外，每个途径的选项都相同。

　　很明显，数据库管理员会经常使用维护计划来备份数据库以及事务日志。但是，如果使用已经设置好日志传送的备份计划来备份事务日志时，在还原过程中会出现问题。最终，会创建出两个事务日志备份集，一个来自备份任务，另一个来自日志传送任务。因此，如果需要执行还原操作，必须两个备份集都有，否则不能把数据库备份到故障点。如果基于日志传送的事务日志备份已经存在，最好不要再通过备份计划另外创建事务日志备份任务。这样不会造成混乱，并不会把恢复计划搞砸而导致数据丢失。

　　3.9 清除维护任务

　　清除维护任务(Maintenance Cleanup Task)用以删除维护计划执行完毕后驻留再数据库中与维护计划相关的文件，包括备份计划文件和文本报告。此任务的选项详细介绍如下：

　　删除以下类型文件——可以选择删除数据库备份文件或以前运行维护计划的文本报告。

　　文件路径——通过文件名对话框可以选择删除指定的文件。

　　搜索文件夹并根据扩展名删除文件——此选项可同时删除某特定文件夹中具有相同指定扩展名(如.txt)的多个文件。还可以选择删除指定文件夹中的所有一级子文件夹。

　　文件保留时间——指定删除保留时间超过指定时间长度的文件。指定时间长度单位可以是小时、天、周、月和年。