SQL维护计划教程.doc

SQL Server 数据库维护计划的详细实施步骤数据库维护计划的详细实施步骤 作为网管员 无论其管理的网络的规模是大还是小 在日常的管理中除了维护 网络平稳运行 及时排除网络故障 保护网络安全等工作以外 备份网络中关 键数据也是其中的一个非常非常重要的工作环节 网络中的各种故障无非就分两种 软件故障和硬件故障 对于 硬件故障 可以 通过维修或更换硬件设备得到及时解决 对于 软件故障 则可以通过重新安装 或升级软件 重做网络或应用软件系统等方法及时解决 而且用此方法来解决 网络故障大多需要一些基础的 关键的数据支持才能得以恢复正常 但是 网 络中诸如此类的关键数据 特别是 应用软件系统 中的关键数据 的损坏或丢 失 绝大部分是无法恢复和弥补的 即使可以恢复部分数据 弥补它们所花费 的代价 诸如时间 人力 财力 物力等 都可能远远超出了公司的承受能力 所以说 注重数据的备份工作是网管员日常管理工作中的必须时刻关注的一项 任务 也是必须周期性重复操作的一项工作 目录 现行备份策略 具体实现步骤 结束语 现行备份策略 我公司在组建局域网时 考虑到商业企业的特点 仔细考量了购 销 存三大 环节中发生的各种数据及其存储问题后 选定了以 Windows 2000 Server 为操 作系统 SQL Server 2000 为数据库平台来搭建局域网的应用系统的软件平台 以网线为载体将购 销 存等核心部门的计算机通过局域网平台紧密地连接起 来 这样 各个核心部门每天的任何一笔业务都及时地 动态地存储到公司局 域网的核心 Dell 服务器上的 SQL Server 2000 数据库中 并以此为基础平台向 各方提供所需的各种数据服务 因此 自公司局域网开始正式运作之日起 作为网管员的我就非常注重对局域 网中的关键数据 特别是这些业务数据的备份工作 同时 我也希望备份数 据的软件能够实现以下自动功能 功能 1 能够在每天的某个固定的时刻 如夜晚 0 00 00 当然这个时间是可以自 主设定的 对包含所有业务数据库在内的所有关键数据库进行一次 完全备份 功能 2 能够在每天的 0 00 00 至 23 59 59 这段时间内 每间隔 1 个小时对功 能 1 中所涉及到的各个数据库的事务日志进行 差异备份 功能 3 每天都能够保留功能 1 和功能 2 中所生成的数据库和事务日志的最近两 天的备份 即 前一天的和前两天的 而且能够自动地删除久于两天前的所有数 据库和事务日志的备份 功能 4 定期 如每个星期一次 将所有关键数据库的完全备份的副本备份到磁带 或其它存储介质上 这部分工作可能需要手工完成 于是 围绕这些功能的实现 在日常管理工作中 我尽可能地尝试了各种备份 数据的软件和方法 如 Windows 2000 自带的 备份 工具 SQL Server 2000 自带的 备份 功能等 这些备份软件和方法的功能各有千秋 但是都存在以 下缺点 一种就是需要人工干预 无法实现自动备份 如 Windows 2000 自带的 备份 工具 如果采用这种方法 就必须时刻人工手动备份 万一哪天因为出差或其 它原因没有进行备份 而这时又出现服务器或数据故障的话 那麻烦就大了 另一种就是能够实现自动备份 但是旧的备份不能被自动地删除 如 SQL Server 2000 自带的 备份 功能 如果采取这种方法 就必须及时地手工删 除旧的备份 否则再大的硬盘也会迅速地被用完 在相互比较后 我还是决定采用第二种 SQL Server 2000 自带的 备份 功能对关键数据库进行备份 因为它能够实现 自动备份 功能 比第一种略 强 所以 在一段时期内 我每天上班后的第一件事就是先检查一下备份目录 下各种数据的新的备份 然后手工删除旧的备份数据 这种做法一度让我很是 苦恼 一天 我在利用 SQL Server 2000 的 帮助 查询某个 Transact SQL 语句的语 义解释时无意中阅读到 自动化管理任务 的内容 从头到尾地仔细阅读后 我不由得眼睛一亮 原来 SQL Server 2000 本身自带了一个能够实现我的备份 要求的 强大的功能 数据库维护计划 于是我立刻按照这部分内容的 提示 以一个数据库为试验样本一步一步地操作 成功地创建了一个数据库维 护计划 经过一个星期的试运行 这个计划果然能够实现自动备份调度 以及 自动删除旧的数据备份 完全能够满足我的备份要求 从那时起 我就利用 SQL Server 2000 的 数据库维护计划 备份所有关键数 据库 而且严格地 定期地执行功能 4 每个星期五将完全备份的数据库备份 到磁带和局域网中其它客户机 主要是用于网络管理的网管 PC 的硬盘上 这样 做的目的是 能同时异地保存三份相同的备份 减少故障带来的损失 而且 通过 SQL Server 2000 的 数据库维护计划 我现在能够较轻松地备 份所需各种数据 方便地管理其备份 相应地减少了日常工作量 也减轻了部 分工作压力 具体实现步骤 目录 第一步 打开 SQL Server 企业管理器 窗体 第二步 找到 数据库维护计划 功能 第三步 创建 数据库维护计划 第四步 维护和管理 数据库维护计划 第五步 启动 SQL Server 2000 代理以便执行 作业 第六步 检查结果 数据库维护计划 功能在 SQL Server 2000 的 企业管理器 中可以找到 说明 1 以下操作是在服务器的 Windows 2000 Server 上进行操作的 在 Window 9X 系统上操作相同 2 由于 SQL Server 2000 执行备份时将产生许多文件 特别是在进行事务日志备 份时 所以建议按数据库名称分别建立独立的备份目录进行存储 3 以下所有操作过程当中一般不会对数据库的使用产生影响 第一步 打开 SQL Server 企业管理器 窗体 用鼠标单击任务栏上的 开始 按钮中的 程序 P 菜单下的 Microsoft SQL Server 子菜单中的 企业管理器 菜单项 即可打开 SQL Server 2000 的 企业管理器 窗体 第二步 找到 数据库维护计划 功能 在 企业管理器 窗体中左侧的树型选项卡中 用鼠标单击 图标扩展开 控制台根目录 下的 Microsoft SQL Servers 可以看到其下有一个 SQL Server 组 接着继续扩展开 SQL Server 组 此时可以看到其下出 现了服务器的名称 图 1 中的 JXNC SERVER 就是我的服务器的名称 再继续 扩展开此服务器 可以看到其下列出了诸如 数据库 数据转换服务 等 项目 最后单击 管理 项目 可以看到其下存在一个 数据库维护计划 如 图 1 单击 数据库维护计划 项目 在 企业管理器 窗口右侧将会显示出已经存 在的维护计划项目 每个维护计划均包括以下项目 1 名称 就是维护计划的名称 此名称可以自定义 中英文皆可 2 数据库 就是维护计划所进行维护的数据库的名称 因为一个维护计划允许同时维护多个数据库 所以此处可以显示出多个数据库 的名称 在图 1 中可以看到名为 系统数据库备份 的数据库维护计划中的 数 据库 就包括三个数据库 master model 和 msdb 3 服务器 也就是维护计划所维护的数据库所处的服务器的名称 local 表示是本地服务器 4 对策 是指维护计划所需要进行的具体维护工作的内容 图 1 中有 3 个 数据库维护计划 均为 数据库备份 事务日志备份 它的 含义就是这些维护计划中同时对所指定的数据库进行 数据库 和 事务日志 的备份 第三步 创建 数据库维护计划 鼠标右击 数据库维护计划 项目 选择 新建维护计划 P 功能 将打开 数据库维护计划向导 窗体 依照此向导能够创建一个新的 数据库维护计 划 步骤 1 单击 下一步 N 按钮 打开 选择数据库 窗体 如图 2 在此窗 体中可以选定一个或多个的数据库作为操作对象 为了叙述方便 我在此只选 择了一个数据库 regie 步骤 2 单击图 2 中的 下一步 N 按钮 打开 更新数据优化信息 窗体 如 图 3 在此窗体中可以对数据库中的数据和索引重新进行组织 以及能够设定在满足 一定条件的情况下 维护计划自动删除数据库中的未使用的空间 以便提高性 能 但要注意的是 在此窗体中 只要选定了 重新组织数据和索引页 R 复选框 更新查询优化器所使用的统计 示例 D 复选框将失效 变成灰色 不能选 择 而且 重新组织数据和索引页 R 复选框和 从数据库文件中删除未使 用的空间 M 复选框二者只要有一个被选中 其下的 调度 S 功能才有效 单击 更改 C 按钮可以对 调度 进行自定义 各位读者可以根据自身情况决定是否选用其中的功能 当然也可以通过单击 帮助 按钮来查看各功能的具体含义 在此窗体中能够便捷地设定每项作业的持续运行时间和运行的频率 完成自己 的设置后 一定要选定右上角的 启用调度 B 复选框 这样一个作业调度才 算真正完成了 步骤 3 单击图 3 中的 下一步 N 按钮 打开 检查数据库完整性 窗体 在此窗体中可以设定维护计划在备份数据库前自动检查数据库的完整性 以 便检测由于硬件或软件错误而导致数据的不一致 在此窗体中只有先选定了 检查数据库完整性 H 复选框 其下 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 数 据库也不算很大 每次运行要两个小时以上 所以都是在凌晨进行 如果进行 的是重新生成索引那么在执行的时候表是无法访问的 现在也没什么更好的解 决方案 这个问题还在继续学习中 解解 SQL Server 数据库维护计划数据库维护计划 因为一个维护计划允许同时维护多个数据库 所以此处可以显示出多个数据库 的名称 在图 1 中可以看到名为 系统数据库备份 的数据库维护计划中的 数据库 就包括三个数据库 master model 和 msdb 3 服务器 也就是维护计划所维护的数据库所处的服务器的名称 local 表示 是本地服务器 4 对策 是指维护计划所需要进行的具体维护工作的内容 图 1 中有 3 个 数据库维护计划 均为 数据库备份 事务日志备份 它的含义就 是这些维护计划中同时对所指定的数据库进行 数据库 和 事务日志 的备份 第三步 创建 数据库维护计划 鼠标右击 数据库维护计划 项目 选择 新建维护计划 P 功能 将打开 数据库 维护计划向导 窗体 依照此向导能够创建一个新的 数据库维护计划 步骤 1 单击 下一步 N 按钮 打开 选择数据库 窗体 如图 2 在此窗体中可 以选定一个或多个的数据库作为操作对象 为了叙述方便 我在此只选择了一 个数据库 regie 图 2 步骤 2 单击图 2 中的 下一步 N 按钮 打开 更新数据优化信息 窗体 如图 3 图 3 在此窗体中可以对数据库中的数据和索引重新进行组织 以及能够设定在满足 一定条件的情况下 维护计划自动删除数据库中的未使用的空间 以便提高性 能 但要注意的是 在此窗体中 只要选定了 重新组织数据和索引页 R 复选框 更新查询优化器所使用的统计 示例 D 复选框将失效 变成灰色 不能选择 而且 重新组织数据和索引页 R 复选框和 从数据库文件中删除未使用的空间 M 复选框二者只要有一个被选中 其下的 调度 S 功能才有效 单击 更改 C 按 钮可以对 调度 进行自定义 各位读者可以根据自身情况决定是否选用其中的功能 当然也可以通过单击 帮 助 按钮来查看各功能的具体含义 在此窗体中能够便捷地设定每项作业的持续运行时间和运行的频率 完成自己 的设置后 一定要选定右上角的 启用调度 B 复选框 这样一个作业调度才算 真正完成了 步骤 3 单击图 3 中的 下一步 N 按钮 打开 检查数据库完整性 窗体 在此窗体中可以设定维护计划在备份数据库前自动检查数据库的完整性 以便 检测由于硬件或软件错误而导致数据的不一致 在此窗体中只有先选定了 检查 数据库完整性 H 复选框 其下的 备份之前执行这些检查 R 和 调度 S 功能才 有效 单击 更改 C 按钮可以对 调度 进行自定义 各位读者可以自主决定 较好的一种做法就是选中 检查数据库完整性 H 复选 框 推荐 因为有可能会修正一些错误 步骤 4 在 检查数据库完整性 窗体中的 下一步 N 按钮 打开 指定数据库备 份计划 窗体 如需对数据库进行备份 则必须选定 作为维护计划的一部分来备份数据库 A 复选框 而且必须指定存储备份文件的位置 磁带 P 或磁盘 K 如果选择 磁盘 K 作为数据库备份的位置 设定 调度 后单击 下一步 N 按钮则 显示 指定备份磁盘目录 窗体 如图 4 图 4 在图 4 中 可以具体指定存储备份文件的目录 可以使用默认的目录 也可自定 义 备份文件扩展名 而且能够指示备份计划自动地删除早于某个时间 图 4 中设定的是 2 天 也就是说两天前的所有备份文件将被自动地删除 只留下最 近两天的备份 的备份文件 而图 4 中的 为每个数据库创建子目录 C 功能只是 在步骤 1 中选择了多个数据库时才有用 对于一个数据库作用不大 设定后 单击 下一步 N 按钮则显示 指定事务日志备份计划 窗体 如果选择 磁带 P 作为数据库备份的位置 设定 调度 后单击 下一步 N 按钮则 直接显示 指定事务日志备份计划 窗体 步骤 5 指定 事务日志备份计划 的过程与步骤 4 的过程完全相同 只是在设定 调度 上稍有差别 因为我的要求是数据库每天备份一次 事务日志每 1 小时备 份一次 步骤 6 对事务日志的备份计划全部设定后 单击 下一步 N 按钮则显示 要生 成的报表 窗体 在此窗体中可以指定用于存放整个备份计划执行过程中的日志的目录 设定过 程与图 4 的操作及其相似 图 5 步骤 7 完成步骤 6 后 单击 下一步 N 按钮则显示 维护计划历史纪录 窗体 在此窗体中可以指定如何存储此维护计划的历史纪录 是存放在 本地服务器 上 还是在 远程服务器 上 而且通过指定表中的行数可以限定历史纪录的存储 大小 步骤 8 完成步骤 7 后 单击 下一步 N 按钮则显示 正在完成数据库维护计划 向导 窗体 如图 5 在此窗体中可以自定义一个 计划名 P 推荐 这样便于管理和识别 当然也可 使用默认的 计划名 P 而且还可以通过对 计划名 P 下的文本框中的内容进 行确认 如有误 则可通过单击窗体中的 上一步 B 按钮退回到相应的窗体进 行修改 步骤 9 完成步骤 8 后 单击 完成 按钮 则显示 维护计划已创建成功 的提 示框 再单击 确定 按钮即成功地设定了一个新的数据库维护计划 从图 6 中可以看到 已经成功的创建了一个新的数据库维护计划 regie 备 份 第四步 维护和管理 数据库维护计划 第三步完成后 对各个 数据库维护计划 的日常维护和管理都非常方便 只需 要双击 数据库维护计划 即可对第三步中所涉及的内容进行变更 修正 图 6 如图 6 所示 鼠标右击 regie 备份 单击 属性 R 或者直接双击 regie 备份 打开 数据库维护计划 窗体 在此窗体中集成了第三步中涉及到的所有功能 每项功能都能任意修改 修改过程与第三步中的相应步骤一样 但需要说明的是 在设定图 7 中的 报表 选项卡下的 文本报表 中的 删除早于 此时间的文本报表文件 F 选项时 也就是第三步中的步骤 6 中的内容 无论您 将其设定成 分钟 小时 天 还是 月 创建成功后都将被自动地更正为 周 而且以后无论如何修改 保存后再去查看时它仍将显示为 周 但不意味 着其它选项无效 其它选项仍然有效 图 7 第五步 启动 SQL Server 2000 代理以便执行 作业 完成第三步后 还需启动 SQL Server 2000 Agent 代理 以便执行 数据库维 护计划 作业 与展开 SQL Server 2000 数据库维护计划 的步骤一样 在 管理 项目中 可 以发现存在一个 SQL Server 代理 如图 8 图 8 单击 SQL Server 代理 下的 作业 子菜单 在 企业管理器 窗口右侧将会显示 出已经存在的作业项目 在图 8 中可以看到已经存在 17 个作业项目 每个作业 项目均包括以下数据列 1 名称 当然是指作业的名称 可以自定义 中英文皆可 为了理解方便 建 议用中英文结合 每当新建立一个 数据库维护计划 将自动生成以下默认名称的作业 1 当新建的 数据库维护计划 中设定了 备份数据库 功能时 将生成默认名为 D B 维护计划 的 DB 备份作业 的作业 2 当新建的 数据库维护计划 中设定了 备份事务日志 功能时将生成默认名为 D B 维护计划 的 事务日志备份作业 多服务器 的作业 以上 1 和 2 中的 处将显示 数据库维护计划 中的 计划名 也就是第三步 步骤 8 中设定的 计划名 2 分类 指明该作业当前所属的类别 缺省值为 未分类 本地 3 启用 指明该作业是否处于 启用 状态 4 可运行 指明该作业是否处于 可运行 状态 5 已调度 指明该作业是否处于 已调度 状态 6 状态 指明该作业当前的运行状态 不在运行 正在运行 7 上次运行状态 开始日期 显示最近一次运行该作业后的状态 已成功 失 败 还是 未知 和运行时的日期和时间 8 下次运行日期 指明下一次运行该作业的日期和时间 如图 8 所示 鼠标右击 regie 完全备份 作业 单击 属性 R 或者直接双击 re gie 完全备份 作业 打开作业的 属性 窗体 在此窗体中集成了该作业的详细的 配置项 每个配置项都能任意修改 属性 窗体中有四个选项卡 常规 在此选项卡中可以重新设定作业名称 名称 N 文本框 修改作业的 分类 分类 Y 下拉框 指定作业的所有者 所有者 W 下拉框 简单地对作业 进行描述 描述 R 文本框 以及决定是否启用此作业 启用 E 复选框 步骤 在此选项卡中可以新建 插入新的步骤 删除 编辑已有的步骤 单击 编辑 E 按钮 在 编辑作业步骤 窗体中的 常规 选项卡中的 命令 M 文本 框中可以查阅到该作业的执行语句 调度 在此选项卡中可以新建调度 新建警报 删除 编辑已有的调度 通知 在此选项卡中可以设定作业完成时 即当作业成功时 作业失败时 执 行的操作 即发送电子邮件 传呼操作员 发出网络警报信息 写入 Windows 应用程序事件日志 自动删除等操作 第六步 检查结果 经过上述五个步骤后 一个完整的备份数据库的计划就建立起来了 可以通过 资源管理器 来检查备份目录下是否存在相应地备份文件 经过长时间的使用 如果以 保留 2 天的数据库完全备份和 2 天的每个一小时的 事务日志备份 的备份策略来正确地建立了一个完整的数据库维护计划的话 无 论何时查看相应备份目录下的文件 都应该存在 102 个文件 2 个数据库的完整备份 即 2 个以 数据库名 db yyyy mmddhhss bak 格式 为文件名的文件 3 个与数据库完整备份相对应的操作过程的记录报告文件 即以 数据库名 备份 4 yyyymmddhhss txt 格式为文件名的文件 48 个事务日志的差异备份 即 2 天各 24 个以 数据库名 tlog yyyymmddhh ss trn 格式为文件名的文件 49 个与事务日志的差异备份对应的操作过程的记录报告文件 即以 数据库 名 备份 6 yyyymmddhhss txt 格式为文件名的文件 以上文件名中 数据库名 为第三步的步骤一中选定的数据库的名称 yyyym mddhhss 是时间戳 其格式为 yyyy 指 年 4 位数值 mm 指 月 2 位数值 不足 2 位的补 0 dd 指 日 2 位数值 不足 2 位的补 0 hh 指 时 2 位 数值 不足 2 位的补 0 ss 指 分 2 位数值 不足 2 位的补 0 结束语 对于一个企业而言 日常运作中发生的各种业务所产生的所有数据 经过计算 机不断地日积月累 逐渐成为公司的一种财富和资本 利用计算机 可以便捷 地统计分析部分或全部的数据 通过各种形式的反馈 如图表 表格等 给公 司的决策层用于参考 便于为公司的今后决策提供指导和帮助 正基于此 这 些数据的价值随着时间的延续正呈现出几何速度的增长 因此我认为 对于数 据的备份工作是网管员日常工作中最重要的工作之一 通过这次 数据库维护计划 的创建 我略有感受 1 经过这么长时间的运用 我认为 数据库维护计划 仍然存在不足之处 虽说 数据库维护计划 功能很强大 但是它最终的结果是生成一项作业 由 SQL Se rver 2000 Agent 服务定期执行它来完成对数据库的备份工作 这就要求 SQL Server 2000 Agent 服务能够正常地 运行 从多次安装来看 在 Windows 2 000 系统中 SQL Server 2000 Agent 服务能够正常运行 而且能随 Windows 2000 的启动自动运行 但是在 Window 98 系统 包括第二版 中 却不能正常 运行 所以说 在 Windows 98 系统中即使依照上述步骤成功地创建了 数据库 维护计划 也会因为 SQL Server 2000 Agent 服务无法启动而变得没有任何 作用 2 之所以选择 完全备份 主要在于 在进行完全备份时 SQL Server 将 mdf 与其对应的 ldf 文件进行对比 删除一些旧的 不必要的日志 然后将 mdf 和 l df 文件进行合并 压缩后一起存储 优点是 能最大可能地 完整地保存数据库 缺点是 存储量随着数据库的增大而增大 存储时间也将随着数据库的增大 而延长 3 在建立 数据库维护计划 过程 各位读者应该尽可能地去使用各种选项 功能 以便加深对 数据库维护计划 的理解和掌握 4 虽然 Internet 上有许多第三方备份软件和工具 但是大多数是共享版 由于 担心知识产权问题 病毒问题和其它问题 所以我没有试用这些第三方软件 这样的话 它们的性能我就不清楚了 也许这些软件的功能非常强大 能够满 足更多的 更高的要求 在这里我只是就 Windows 2000 和 SQL Server 200 0 自带的 备份 工具和软件进行一个比较 高效维护数据库的关键技巧 Paul S Randal 概览 概览 管理数据和事务日志文件 清除索引碎片 确保统计数据准确 最新 检测遭到破坏的数据库页 建立有效的备份策略 目录 数据和日志文件管理 索引碎片 统计数据 损坏检测 备份 总结 在一周之内多次有人向我征求高效维护生产数据库的建议 有时问题来自 DBA 他们正在实施新的解决方案 希望得到帮助 对维护进行精细调整适合其新数据库的特点 但更为常见的情况是 提问的人 不是专业 DBA 而是由于某种原因拥有数据库并承担相关责任的人员 我喜 欢将这种角色称为 非自愿 DBA 本文重点是为所有非自愿 DBA 提供数据 库维护最佳实践的入门知识 在 IT 世界里 大多数任务和程序都没有一个简单 通用的解决方案可以高效 维护数据库 但却有一些必须受到重视的关键领域 我所关心的五大重要领域 是 没有任何特殊的重要性顺序 数据和日志文件管理 索引碎片 统计数据 损坏检测 备份 一个未经维护 或维护不良 的数据库可能会在其中的一个或多个领域内引发 问题 最终可能导致应用程序性能欠佳 甚至是停机以及丢失数据 在本文中 我将说明这些问题很重要的原因并向您展示一些缓解这些问题的简 单方法 我将以 SQL Server 2005 为基础进行说明 但我还会着重指出您 将会在 SQL Server 2000 和即将发布的 SQL Server 2008 中发现的主要 差别 数据和日志文件管理数据和日志文件管理 我始终建议在接管数据库时检查的第一个领域涉及到与数据和 事务 日志文 件管理相关的设置 具体地说 您应确保 数据和日志文件彼此分开 而且还与其他所有内容相互隔离 自动增长已正确配置 即时文件初始化已配置 自动缩减未启用而且缩减不是任何维护计划的内容 当数据和日志文件 理想情况下应分别位于不同的卷中 与其他任何创建或扩 展文件的应用程序共享一个卷时 可能存在文件碎片 在数据文件中 过多的 文件碎片可能是导致查询 特别是扫描非常多数据的查询 效果不佳的一个因 素 在日志文件中 它可能会对性能产生相当大的影响 尤其是在自动增长设 置为需要增加每个文件的大小时 增量很小的情形 日志文件在内部被划分为多个称为 虚拟日志文件 VLF 的片段 而且日志文 件 我在这里使用单数是因为拥有多个日志文件并没有任何好处 每个数据库 只应有一个日志文件 内的碎片越多 VLF 就越多 一个日志文件具有多个 比方说 200 个 VLF 后 与日志有关的操作 如为事务性复制 回滚而读 取日志 日志备份乃至 SQL Server 2000 中的触发器 触发器的实现已在 SQL Server 2005 中更改为行版本框架 而不是事务日志 可能会对性能产 生负面影响 调整数据和日志文件大小的最佳做法是创建它们时使用适当的初始大小 对于 数据文件 初始大小应考虑短期内向数据库中添加其他数据的可能性 例如 如果数据的初始大小为 50GB 但您知道在接下来的六个月内将再添加 50GB 的数据 那么应创建 100GB 的数据文件 而不是多次将其增大以达到该大小 对于日志文件而言要更复杂一些 您需要多考虑一些因素 例如事务大小 长 时间运行事务在完成之前无法从日志中清除 以及日志备份频率 因为这将删 除日志的非活动部分 有关详细信息 请参阅我的妻子 Kimberly Tripp 编 写的一篇很受欢迎的博客文章提高事务日志吞吐量的 8 个步骤 它发表在 SQL 上 设置一旦完成 应不时监视文件大小 并在每一天的适当时间先行手动增加其 大小 为以防万一 应保留自动增长 这样文件即使在发生一些异常事件的情 况下仍可以完成所需的增长 反对将文件管理完全保留为自动增长的逻辑是步 长极小的自动增长会导致文件碎片 而且自动增长会是一个耗时的过程 它可 能会多次突然停止应用程序的工作 应将自动增长大小设置为一个具体值 而不是一个百分比 以约束执行自动增 长 如果发生 所需的时间和空间 例如 您可能希望将一个 100GB 的数据 文件的自动增长大小设置为固定值 5GB 而不是 比方说 10 这意味着 无论文件每次变得多大 它均将按 5GB 进行增长 而不是一个持续增长的数 量 10GB 11GB 12GB 等 当事务日志增长时 手动或自动增长 它将始终被初始化为零 数据文件在 SQL Server 2000 中具有同一默认行为 但从 SQL Server 2005 开始 您可以启用即时文件初始化 它会跳过零初始化文件 因此增长和自动增长会 保持同步 所有版本的 SQL Server 中都提供了这一功能 这一点与正常的 观点恰恰相佐 如欲了解详细信息 请在 SQL Server 2005 或 SQL Server 2008 的联机丛书索引中输入 即时文件初始化 最后 应注意不要以任何方式启用缩减 缩减可用于减小数据或日志文件的大 小 但它是一个干扰很大 极耗资源的过程 会导致数据文件中产生大量的逻 辑扫描碎片 详细信息请参见下文 并导致性能欠佳 我更改了 SQL Server 2005 联机丛书中的缩减条目 加入了一个警告 提醒注意此影响 但在特殊情况下 允许手动缩减单个数据和日志文件 视频 观看 Paul Randal 演示缩减和自动缩减如何导致数据库发生严重的碎片问题 自动缩减的后果极为严重 因为它每 30 分钟就会在背景中启动一次 并会尝 试缩减自动缩减数据库选项被设置为 true 的数据库 从某种程度讲 它是一 个无法预见的过程 因为它仅缩减拥有 25 以上可用空间的数据库 自动缩 减占用大量资源 会产生碎片并导致性能下降 因此在任何情况下都不是一个 好计划 您应始终通过以下方式关闭自动缩减 复制代码 ALTER DATABASE MyDatabase SET AUTO SHRINK OFF 包含手动数据库缩减命令的定期维护计划几乎会产生同样糟糕的结果 如果您 发现您的数据库在维护计划将其缩减后持续增长 原因在于数据库运行需要该 空间 最好的做法是允许数据库增长到稳定的大小 尽量避免运行缩减 您可以在我 原来的 MSDN 博客 位于 t aspx 中找到有关使用缩减的缺点的详细信息 其中还有对 SQL Server 2005 中新算法的一些评论 索引碎片索引碎片 除了文件系统级和日志文件内的碎片以外 数据文件内存储表格和索引数据的 结构中也可能存在碎片 数据文件内可能出现两种基本类型的碎片 单个数据和索引页内的碎片 有时称为内部碎片 由页面组成的索引或表格结构内的碎片 称为逻辑扫描碎片和扩展盘区 扫描碎片 内部碎片是页面中存在大量空白区域的位置 如图图 1 所示 数据库中的每个 页面大小为 8KB 页眉为 96 字节 因此 一个页面可以存储大约 8096 字 节的表格或索引数据 我的博客 有 深入了解存储 引擎 一节 其中介绍了数据和行结构的特定表格和索引内部机制 如果每个 表格或索引记录超过页面大小的一半 可能会出现空白空间 因为每个页面只 能存储一个记录 这可能很难或无法更正 因为它要求更改表格或索引架构 例如 将索引键更改为像 GUID 一样不会引发随机插入点 图 1 数据库页的结构数据库页的结构 单击图像可查看大图 更常见的是 内部碎片源于数据修改 如插入 更新和删除 这可能会在页 面中留下空白空间 管理不善的填充因子也可能会产生碎片 有关详细信息 请参阅 联机丛书 根据表格 索引架构和应用程序的特征 此空白空间在其创 建后可能就不再使用 导致数据库中的不可用空间量持续增长 例如 我们来看一下一个 1 亿行表格 其中平均记录大小为 400 字节 后来 应用程序的数据修改模式为每个页面留下了平均 2800 字节的空白空间 该表 格所需的总空间为 59GB 计算方法为 8096 2800 400 13 个记录 8KB 页面 然后将 1 亿除以 13 便可获得页面数 如果没有空间浪费 那 么每个页面刚好容纳 20 条记录 所需的总空间下降至 38GB 这是多么大的 节省 如数据 索引页中出现空间浪费 可能导致存储同样数量的数据需要更多的页面 这不仅会占用更多的磁盘空间 还意味着查询需要执行更多的 I O 才能读取 同样数量的数据 所有这些多出的页面在数据缓存中占用了更多空间 因而占 用了更多的服务器内存 逻辑扫描碎片是由称为页面分隔的操作而引发的 当必须在特定索引页 根据 索引键定义 中插入记录但页面中并没有足够的空间来容纳所插入的数据时 便会发生这种情况 该页面会被分割一半 大约 50 的记录被移到新分配的 页面 通常 这一新页面实际上并不与旧页面相邻 因此 被称为零碎的页面 扩展盘区扫描碎片在概念上与此类似 表格 索引结构内的碎片会影响 SQL Server 执行有效扫描的能力 无论是对整个表格 索引进行扫描还是按查询 WHERE 子句 例如 SELECT FROM MyTable WHERE Column1 100 AND Column1 4000 进行扫描都会受到影响 图图 2 显示的新建索引页填充率是 100 没有碎片 这些页面完全被充满 而且页面的物理顺序与逻辑顺序相符 图图 3 显示了在随机插入 更新 删除之后 出现的碎片 图 2 不带碎片的新建索引页 页面被完全填充不带碎片的新建索引页 页面被完全填充 单击图像可查看大图 图 3 随机插入 更新和删除之后出现内部和