Server保驾护航的正确维护4步骤-SQL (server2012)

技术教程 2025-04-19 06:30:08 浏览次

此文章主要向大家讲述的是正确维护SQL Server保驾护航的实际操作步骤，我前两天在相关网站看见正确维护SQL Server保驾护航的实际操作步骤的资料，觉得挺好，就拿出来供大家分享。

每个新发布的SQL Server都具有越来越多的自我维护SQL Server保驾护航能力，但是“安全比抱歉好得多”的原则仍然是正确的:实际的数据库管理包含了对系统和用户数据库进行的周期性维护，这样才能在你的用户发现问题之前把它解决掉。

SQL Server 2000中包含了数据库维护计划向导，它可以为你自动完成所有的维护任务。然而，你需要了解，在现场和你的维护计划后面，什么是隐藏的可能给你带来麻烦的东西。

数据库维护计划是通过一系列的DBCC命令和系统存储过程实现的。DBCC的意思是数据库一致性检测(database consistency check)或者数据库控制台命令(database console command)。有非常多的写入文档的和没有写入文档的DBCC命令，但是只有一小部分可以用于维护SQL Server保驾护航。这里我将回顾一些与数据库维护SQL Server保驾护航的各个方面相关的DBCC命令和系统存储过程。

通常的数据库维护时间表都具有如下的活动。点击活动，可以获得与此相关的DBCC命令和系统存储过程。

1、检测数据库一致性和数据的完整性

DBCC CHECKDB是最广泛使用的，检测数据库中所有对象的工具。这个语句可以为每一个表和索引视图，以及文本和图像对象，检测所有的数据和索引页面的分配和结构上的完整性。DBCC CHECKDB保证了所有的数据和索引页面都正确链接，并且指针都是一致的。在指定的数据库中，用户和系统表都会被检测到。DBCC CHECKDB对数据库中每个对象都都执行DBCC CHECKALLOC和 DBCC CHECKTABLE语句，所以如果你使用DBCC CHECKDB，你就不需要再执行DBCC CHECKALLOC和DBCC CHECKTABLE了。

DBCC CHECKDB的某些选项(REPAIR_FAST, REPAIR_REBUILD 和 REPAIR_ALLOW_DATA_Loss)需要数据库在单用户的模式下运行。如果不是单用户模式的化，语句就失败了。注意，当用户连接在上面的时候，不要将数据库设置成单用户模式。

如果你执行不带参数的DBCC CHECKDB并且发现错误的时候，你应该将你的数据库设置在单用户模式，然后尝试修复这个问题。首先，确保执行那些不会导致数据丢失的语句——REPAIR_FAST 和 REPAIR_REBUILD。如果你还是遇到错误，那么执行DBCC CHECKDB，带着参数REPAIR_ALLOW_DATA_LOSS。还要确保在显性事务中关闭语句。如果发生了可接受的数据丢失，你可以提交这个事务。否则，你还可以通过语句来回滚所作的修改。

请注意，运行DBCC CHECKDB是一项非常消耗资源的操作。你应该在限制用户在数据库服务器上的活动的时候运行这个语句。

DBCC CHECKTABLE与DBCC CHECKDB相同，除了它是在一个单个的表、索引视图或者即使是一个索引上，而不是在整个数据库上。

DBCC CHECKALLOC检测某个数据库的磁盘空间分配结构的一致性。因为DBCC CHECKDB包括了与DBCC CHECKALLOC同样的检测，那么如果执行了CHECKDB的话就没有必要再执行DBCC CHECKALLOC了。实际上，我们推荐只使用DBCC CHECKALLOC，如果用DBCC CHECKDB或者 DBCC CHECKTABLE会报告说产生分配错误的话。

DBCC CHECKCONSTRAINTS在某个数据库中，检测某些特定的约束或者全部约束的一致性。DBCC CHECKCONSTRAINTS总是在当前数据库的上下文环境中执行。

注意，DBCC CHECKCONSTRAINTS并不进行磁盘或者文件级别的一致性检测;它只是确保外键定义的一致性，同时检测约束——仅仅是确认数据有效。如果你希望检测磁盘上表和索引的一致性，你应该执行DBCC CHECKDB或者在所有的表上执行DBCC CHECKALLOC和 DBCC CHECKTABLE的组合。

首先，为什么会发生约束违规?当数据库创建的时候，外键和一致性检测也许并不存在。开发人员和数据库管理员也许使用了WITH NOCHECK选项来创建约束，这个选项只能防止约束违规的进一步扩展，而不是会检测已经存在的数据。更进一步的说，通过外键链接的数据可能会过期并且从母表中删除，但是仍然会留在相关的表中，因为它在下级表中仍然具有相关记录。

DBCC CHECKCATALOG在某个数据库的系统表内或者之间检测一致性。很多类似DBCC CHECKCONSTRAINTS的命令都不会检测页面分配的一致性;它只是检测系统表中的数据。DBCC CHECKCATALOG报告错误意味着有些人手工从系统表中添加、修改或者删除记录了。如果你没有注意此类活动，那么你应该看紧你的安全措施了——看看谁具有系统管理员和数据库主任的全县，然后评估你的安全策略。

2、重建索引

当数据行从表中INSERTED, UPDATED and DELETED的时候，索引就产生了碎片。碎片越多，索引的效率越低。数据库管理员必需确保碎片的级别很低或者根本不存在。碎片级别可以通过在某个索引上执行DBCC SHOWCONTIG语句来找到。

这里有三种方法可以去掉碎片:

1、使用CREATE INDEX……WITH DROP EXISTING语句来删除并重新创建索引

2、执行DBCC DBREINDEX

3、执行DBCC INDEXDEFRAG

DBCC DBREINDEX重新构建一个特定的索引或者某个特定的表上的所有的索引。这个语句允许强制PRIMARY KEY和UNIQUE约束的索引重新构建，而不需要删除约束。你不需要知道索引的类别和名称，你同样可以使用。使用DBCC DBREINDEX比单独为表上的每个索引编写DROP INDEX 和CREATE INDEX语句简单。时刻记住，重新构建聚簇索引也会引起非聚簇索引的重建。

DBCC INDEXDEFRAG可以删除某个聚簇索引或者非聚簇索引的碎片。与DBCC DBREINDEX不同，这个语句需要指定某个特别的索引，并且不能运行在表上所有索引上。删除碎片也是一项在线操作，因此不会妨碍用户对表进行操作。DBCC INDEXDEFRAG给系统增加了额外的负担，因为它产生了额外的I/O负担。它还会影响到索引页面，并且会在压缩之后删除所有遗留的没有数据的页面。

删除碎片的频率依赖于在你的数据库中，数据修改的级别。需要每天处理几百万个事务的系统应该至少每个星期都进行一次索引重建。另一方面，在几乎没有修改的数据库上，即使你每个月进行一次索引重建，数据库都会运行得不错。

3、更新统计数字

统计数据中包含了表中某个索引或者字段的数值分布的信息。你可以通过使用CREATE STATISTICS语句或者使用sp_createstatistics系统过程来创建统计数字。统计数字检索有关索引是否具有良好或者糟糕的选择性的信息，索引的选择性可以用来判断索引的效率是否足以满足查询的要求。当你创建索引的时候，SQL Serve保驾护航r自动创建统计数字。此外，SQL Server还为那些没有定义索引的字段创建统计数字。

针对某个索引的统计数字可以通过使用DBCC SHOW_STATISTIC0S语句来查看。当数据发生改变的时候，统计数字就过时了。当选择索引来满足查询的时候，陈旧的统计数字会让SQL Server作出次优化的决定。

默认情况下，SQL Server 2000自动更新每个表上的统计数字。然而，在某些情况下，关闭统计数字的自动更新是有意义的。例如，我们假设你有某种类型的批处理例程，每个周末，系统具有最小利用率的时候，都会向你的表中添加上百万行数据。统计数字的自动更新只会降低你的批处理过程，而不会为系统带来任何好处。除了你可以在周末关闭自动更新，然后在每个周一早上首先更新统计数字之外，你还可以启用或者禁用统计数字的自动更新，通过使用sp_autostats过程。

如果你想要为单个的表或者索引更新统计数字，你可以使用UPDATE STATISTICS命令。或者你还可以执行sp_updatestats系统过程来更新当前数据库中所有表上的统计数字。

4、报告数据和日志文件中的空间利用率

也许你被要求扩展或者缩减一个数据文件或者事务日志文件的尺寸，那么这时候就可以使用DBCC SHRINKDATABASE 或者 DBCC SHRINKFILE命令了。

Sysindexes表在经过一段时间之后会变得不准确，特别是在增长频繁并且/或者缩减频繁的数据库中。DBCC UPDATEUSAGE命令报告并且纠正sysindexes表中不准确的数字。如果你认为你的数据库或者表的尺寸与sp_spaceused系统过程中报告的数字不符的话，那么你就应该使用这个语句。

每次在你使用DBCC SHRINKDATABASE 或者 DBCC SHRINKFILE缩减数据库文件之后，都执行DBCC UPDATEUSAGE，或者只是作为一个周期性的维护SQL Server保驾护航计划，都是个好主意。

【编辑推荐】

sql server 2008数据库维护有哪些

一：目标任务：使用SQL Server 2008自带的维护计划自动备份数据库，并且自动删除超过设定期限的历史备份文件：1.每天0点自动备份指定数据库 testDB，备份文件放在E:\autoBack文件夹下2.备份文件保留1周，每天备份完数据库后自动删除超过1周的备份文件二：前提条件1. 已经安装SQL Server Integration Services 服务2.已经安装SQL Server 代理 (MSSQLSERVER) 服务如果未安装SQL Server Integration Services 服务，依然可以创建维护计划，但是不会生效三：创建维护计划（使用维护计划向导）1. 选择对象资源管理器下的“管理”，点击右键，选择“维护计划向导”2. 选择“下一步”进入“选择计划属性”界面，我们这里修改计划名称为 testMaintenancePlan因为我们现在有两个任务，一个是备份数据库testDB，一个是删除历史备份文件。所以选择“整个计划统筹安排或无计划”3. 点击“更改”按钮，进入时间计划设置界面我们这里需要每天重复执行，频率是每天0点钟执行，从当天开始执行4.点击“确定”后，返回计划属性界面5.选择“下一步”，进入选择维护任务界面我们这里有两个任务，备份数据库和删除超期的历史备份文件，所以勾选“备份数据库（完整）”和“‘清除维护’任务”两个任务6. 选择“下一步”，进入设置维护任务顺序的界面这里的顺序是先备份数据库，再执行“清除维护”任务，删除历史备份7. 选择“下一步”，进入备份数据库任务的设置界面选择指定的特定数据库testDB：指定备份文件存放的目录为E:\autoBack备份文件扩展名为bak（默认），bak字母前面不要加点( . )8. 选择“下一步”进入“定义清除维护任务”界面我们需要删除文件夹（E:\autoBack）下面的以bak为扩展名的数据库备份文件，文件保留时间为1周，这里需要注意的是，文件扩展名填“bak”而不要填“”，扩展名前不要加点，否则不会删除9. 选择“下一步”设置报告文件路径10.选择“下一步”完成11.刷新“管理” 下的“维护计划”文件夹，可以看到创建的维护计划12.双击新建的维护计划或点击右键选择“修改”，可以看到维护计划的内容，并修改，修改后点击保存即可13. 最后重启SQL Server 代理服务即可

sqlserver 维护计划怎么添加 sql

步骤1：打开企业管理器，如下。 2步骤2：点击管理，新建数据库维护计划，进入向导，选择下一步。 3步骤3：选择数据库PUBS，点击下一步。 4步骤4：更新数据库优化信息。可以进行数据库文件空间设置。我们点击更改进行调度设置。 5步骤5：可以通过修改调度，对作业时间参数进行设置。步骤6：检查数据库完整性。步骤7：指定数据库备份计划。步骤8：可以编辑调度。步骤9：指定备份目录，可以选择路径，删除时间和扩展名。步骤10：指定事务日志备份计划。步骤11：报表。步骤12：维护计划历史。步骤13:数据库维护计划完成向导。步骤14：数据库维护设置的最终结果如下：数据库 pubs服务器 (local)优化每 1 周在星期日发生，在 1:00:00。执行下列操作: 当数据库的大小超过 50 MB 时收缩数据库。保留 10 % 的数据空间作为可用空间。 INTEGRITY CHECKS 每 1 周在星期日发生，在 0:00:00。执行下列操作: 检查数据库完全备份每 1 周在星期日发生，在 2:00:00。备份媒体: 磁盘备份目的: C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP 删除 4 周之前的备份文件。完成后验证备份。为每个数据库创建子目录以便存储备份文件。事务日志备份每 1 周在星期一，星期二，星期三，星期四，星期五，星期六发生，在 0:00:00。备份媒体: 磁盘备份目的: C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP 删除 4 周之前的备份文件。完成后验证备份。为每个数据库创建子目录以便存储备份文件。生成并存储报表的目录为: C:\Program Files\Microsoft SQL Server\MSSQL\LOG\步骤14：数据库维护计划完成。