"当数据库出现页损坏或校验和出错时如何处理,

作者: www.9159.com  发布:2019-09-27

问题背景

运维操作失误,在没有正常关闭sqlserver的情况下,将服务器关闭了,重启后某些表损坏(应该是某些页损坏了,没有损坏的页还能访问到数据,但是访问损坏了的页就有问题),目前数据库只有4.20号的备份。

 

MS Sql Server 提供了很多数据库修复的命令,当数据库质疑或是有的无法完成读取时可以尝试这些修复命令。

当数据库出现页损坏或校验和出错时如何处理

报错信息

查询脚本:select * from t_jxjs_pctq where c_bh_tqxx = '8ae480b26320550e016323d098050175';

报错信息:HY000-[SQL Server] 数据库 ID 11,页[1:60682]已标记为RestorePending,可能表名磁盘已损坏,要从此状态进行恢复,请执行还原操作。

MS Sql Server 提供了很多数据库修复的命令,当数据库质疑或是有的无法完成读取时可以尝试这些修复命令。

  1. DBCC CHECKDB

作者:nzperfect / perfectaction
日期:2009.09.27

报错可能的原因

RestorePending一般是在进行页恢复的过程中出现的,就是在进行了restore操作之后但还没有进行recovery操作之前页的状态。出现这样的问题可以肯定这个表是损坏了,但是在查询数据的时候如果不会查询到损坏页面的数据话是不会报错的,也就是说可以有条件的使用这个表。参考资料

  1. DBCC CHECKDB

重启服务器后,在没有进行任何操作的情况下,在SQL查询分析器中执行以下SQL进行数据库的修复,修复数据库存在的一致性错误与分配错误。

最近一直在进一步学习数据库故障的处理方面的知识,做为一个数据库维护人员,我即期望遇到所有的数据库出错的案例,以增加自己的经验,但同时又担心遇到这样或那样无法处理的数据库故障而导致数据丢失。

5.7号和4.20号的数据量对比

表名 4.20号 5.6号
T_JXJS_PCTQ 1716 2175
T_YWGY_WSQD_WS 7358 8275
T_JXJS_HYJL 244 287

重启服务器后,在没有进行任何操作的情况下,在SQL查询分析器中执行以下SQL进行数据库的修复,修复数据库存在的一致性错误与分配错误。

use master

前几天看到一个文章,是说一个网站管理员在招聘DBA时,提出一个问题:“如果在sql server 日志里发现一个页损坏或是校验和错误应该如何处理?”网站管理员描述,大概有90%的应聘者都会采用一个方案,用DBCC CHECKDB加上其中的一个修复选项,但其中也基本没有人能具体解释DBCC CHECKDB修复的过程或是工作原理及能修复到什么程度。

数据库修复

--修复改数据库 1.此时我们需要将数据库设置成单用户模式:
右键点击数据库 -> 属性 -> 选项 -> 状态 -> 限制访问 -> 选择Single-> 确定。注意修复完成后需要改回多用户模式。
--2.使用dbcc checkdb进行数据库修复
DBCC CHECKDB ('db_xfzx', REPAIR_FAST) 
--修复过程中报错信息:
T_JXJS_HYJL的 DBCC 结果。
消息 8928,级别 16,状态 2,第 1 行
对象 ID 885578193,索引 ID 1,分区 ID 72057594060341248,分配单元 ID 72057594075873280 (类型为 In-row data): 无法处理页 (1:70890)。有关详细信息,请参阅其他错误消息。
        DBCC 语句的修复级别导致避开了此修复。
消息 8939,级别 16,状态 98,第 1 行
表错误: 对象 ID 885578193,索引 ID 1,分区 ID 72057594060341248,分配单元 ID 72057594075873280 (类型为 In-row data),页 (1:70890)。测试(IS_OFF (BUF_IOERR, pBUF->bstat))失败。值为 12584969 和 -6。
        修复此错误要求首先修正其他错误。
消息 8976,级别 16,状态 1,第 1 行
表错误: 对象 ID 885578193,索引 ID 1,分区 ID 72057594060341248,分配单元 ID 72057594075873280 (类型为 In-row data)。在扫描过程中未发现页 (1:70890),但该页的父级 (1:704) 和上一页 (1:450709) 都引用了它。请检查以前的错误消息。
        修复此错误要求首先修正其他错误。
对象 'T_JXJS_HYJL' 的 6 页中有 249 行。
CHECKDB 在表 'T_JXJS_HYJL' (对象 ID 885578193)中发现 0 个分配错误和 3 个一致性错误。
​
--3.重建索引并修复,报一样的错
DBCC CHECKDB ('db_xfzx', REPAIR_REBUILD)
​
--4.在修复过程中发现T_YWGY_WSQD_WS,T_JXJS_HYJL均有此报错。同时检查其他库没有发现有损坏情况。
​
--5.尝试进行单个表修复,以及对损坏页的单独修复,均会报上面的的错。
dbcc checktable('t_jxjs_pctq',REPAIR_REBUILD) 
dbcc page(11,1,60682,3)

dbcc checkdb并未能解决问题。

use master

declare @databasename varchar(255)

借助联机文档以及个人的一些理解和经历,解释一下如何面对这个问题:"当数据库出现页损坏或校验和出错时如何处理?"

重建索引

1.执行了dbcc checkdb后,报错的信息里有索引 ID 1;这个信息的提供,可能是索引页的损坏。但是前面执行的DBCC CHECKDB ('db_xfzx', REPAIR_REBUILD)重建索引修复,并没能解决问题。

2.猜测:因为一个表中有多个索引,所以是不是单独重新生成每一个索引就能发现是哪个索引有问题呢?

3.在sqlserver客户端工具上面,对表T_JXJS_HYJL包括主键在内的三个索引进行重新生成,过程中有一个普通索引(I_JXJS_PCTQ_TQXX)的重新生成失败了,报错信息和最开始查询的信息一样。尝试重新组织该索引还是一样的问题。那么问题就出在I_JXJS_PCTQ_TQXX这个普通索引上了。

4.既然重建索引失败了,尝试删除该索引,发现可以删除,再重新创建该索引。

5.重建完成后再修复,DBCC CHECKDB ('db_xfzx', REPAIR_FAST) 。这时异常信息里面没有T_JXJS_HYJL表的异常信息。查看表中的数据已经正常,异常的数据可以正常查询,数据量的统计也已经正常。

6.同样T_YWGY_WSQD_WS该表有一个普通索引重新生成有问题,采用上面的方法也能解决。而T_JXJS_HYJL这张表的数据出现重建异常的是主键,由于有主键约束,所以不能删除索引,尝试修改为非主键,但是报错和查询一样的的错误。看来主键的数据不能这么做。最终由于该表只有两百多条数据,而且并不重要,直接恢复了4.20号的数据。

7.当然对表T_YWGY_WSQD_WS也可以采用将该表的数据通过select * into tableA from tableB;的形式插入到另外的表,重新创建该表后将数据恢复回来,然后重建索引。

declare @databasename varchar(255)

set @databasename='需要修复的数据库实体的名称'

首先,需要先了解DBCC CHECKDB,联机文档url:

结语

1.运行dbcc checkdb(db_name)检查数据库的完整性。根据日志判断可能由于某个索引的索引页缺失,索引不完整,导致某些数据查询的异常。而重新生成索引,不能成功,可以先删除该索引,再重新创建。

2.如果是主键索引则可以采用数据迁移的方式。

3.需要注意的是修复过程中不要使用DBCC CHECKDB ('数据名'', REPAIR_ALLOW_DATA_LOSS),REPAIR_ALLOW_DATA_LOSS该语句是可能丢失数据的。

4.修复完成后需要从单用户模式修改为多用户模式。

5.做到未雨绸缪,提前做好备份,每天备份,对备份的数据进行还原测试。做到有”备”无患

 

 

set @databasename='需要修复的数据库实体的名称'

 exec sp_dboption @databasename, N'single', N'true' --将目标数据库置为单用户状态

通过联机文档,可以得知有REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD三个修复选项,而提供实际功能的只有REPAIR_ALLOW_DATA_LOSS和REPAIR_REBUILD两个,其 中REPAIR_ALLOW_DATA_LOSS 尝试修复报告的所有错误,这些修复可能会导致一些数据丢失;而且REPAIR_REBUILD执行不会丢失数据的修复,包括快速修复(如修复非聚集索引中 缺少的行)以及更耗时的修复(如重新生成索引);可见REPAIR_REBUILD是我们期望的图片 1
当你从sql server log里或是在程序查询数据库或是定期通过DBCC CHECKDB为数据库做体检的时候,出现了页损坏或校验和出错信息时,如:

exec sp_dboption @databasename, N'single', N'true' --将目标数据库置为单用户状态

dbcc checkdb(@databasename,REPAIR_ALLOW_DATA_LOSS)


dbcc checkdb(@databasename,REPAIR_ALLOW_DATA_LOSS)

dbcc checkdb(@databasename,REPAIR_REBUILD)

M8928sg , Level 16, State 1, Line 1
Object ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unit ID 72345201051571606 (type In-row data): Page (1:94299) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unit ID 72345201051571606 (type In-row data), page (1:94299). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'yourtable' (object ID 2088535921).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'yourdb'.

dbcc checkdb(@databasename,REPAIR_REBUILD)

exec sp_dboption @databasename, N'single', N'false'--将目标数据库置为多用户状态

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (yourdb).

现在我们应该如何做?

1.通过上面的提示,告诉我们:对象 2088535921出错,它是一个表,页面为1:94299
2.接下来,我们判断损坏的页在堆上还是聚集索引还是非聚集索引,sql server方法为:

    dbcc traceon (3604, -1)
    go
    dbcc page('yourdb', 1, 94299, 3)
    go

  在输出的结果里(会报错,但可以看到页头信息),可以看到

    Metadata: IndexId = n 

  如果n是0而表示是堆,1表示是聚集索引,>1是表示非聚集索引
  ps:其实从提示信息的Object ID 2088535921, index ID 0 ,也可以简单判断是堆.
3.根据上面的第2步,我们知道这个页面是堆,这对我们来讲,不是好消息,因为如果是>1,我们可以删除该非聚集索引,再重建索引,不会丢失数据,而0或1则是元数据受损,这意味着有丢失元数据的可能性。
  那么如何仅仅修复这个数据页呢,这里我们假设该库是full模式,并且有良好的备份策略,有全备和日志备份。
  那么我们可以进行页面级还原操作,步骤如下:

  a.首先进行一次日志备份,如果你不放心,还可以再做一个全备;
    backup log yourdb to disk='D:DBBakyourdb_a.trn'
  b.通过完整备份来恢复该page. (yourdb.bak是一个全备。);
    restore database yourdb page= '1:94299' from disk='D:DBBakyourdb.bak' with norecovery
  c.恢复这个全备之后的差异(假设有差异yourdb.dif),如果没有差异备,直接到d步骤;
    restore database yourdb from disk='d:DBBakyourdb.dif'  with norecovery
  d.恢复之后的log备份,可能有多个(假设为yourdb_1.trn,yourdb_2.trn);
    restore log yourdb from disk='d:DBBakyourdb_1.trn' with norecovery
    restore log yourdb from disk='d:DBBakyourdb_2.trn' with norecovery
    restore log yourdb from disk='d:DBBakyourdb_a.trn' with norecovery
  e.做一个最新的日志备;
    backup log yourdb to disk='D:DBBakyourdb_e.trn'
  f.还原最后的(e步骤)日志备份;
    restore log yourdb from disk='d:DBBakyourdb_e.trn' with recovery
  g.结束
4.经过步骤三之后,我们再来检查一下该表是否还有错,从提示信息Object ID 2088535921里,我们查出表名tbname;
    tbname: select object_name(2088535921)
    然后 dbcc checktable('yourtable')检测,如果没有报错,则表示修复完成
5.最后,对整个库再做一次dbcc checkdb检查;

ps:需要注意的是,sql server 的page级恢复在企业版和开发版中,支持联机恢复page数据,在标准版只能脱机修复;
在dbcc checkdb修复选项里,用repair_rebuild修复数据,联机文档称是不丢失数据,但在某些环境下可能也会丢失数据,不过,我没遇到过:)
用repair_allow_data_loss选项时,联机文档称可能会丢失数据,而对于堆或聚集索引的页损坏,sql server 会释放该页面,造成数据的丢失,但repair_allow_data_loss选项有两种情况是不会丢失数据,一种是非聚集索引上的页错误,另外是lob页数据错误。

总绍:
一定要有良好的数据库备份策略,备份重于一切;
要有异机备份,并且时时同步该备份文件;
当数据库出现故障时,不要过于心急,冷静分析一下错误;
如果不能确定如何做,可以借助google,如果你的错误信息里中文的,请翻译成英文后再google,这样搜到解决方案的可能性更大;
做修复时,一定要再备一次数据库;
dbcc checkdb的repair_allow_data_loss选项永远是最后的选择。

结束,如有错误,请指正。

 

exec sp_dboption @databasename, N'single', N'false'--将目标数据库置为多用户状态

然后执行 DBCC CHECKDB('需要修复的数据库实体的名称') 检查数据库是否仍旧存在错误。注意:修复后可能会造成部分数据的丢失。

 

  1. DBCC CHECKTABLE

然后执行 DBCC CHECKDB('需要修复的数据库实体的名称') 检查数据库是否仍旧存在错误。注意:修复后可能会造成部分数据的丢失。

如果DBCC CHECKDB 检查仍旧存在错误,可以使用DBCC CHECKTABLE来修复。

 

use 需要修复的数据库实体的名称

  1. DBCC CHECKTABLE (本人已经使用过此方法)

declare @dbname varchar(255)

如果DBCC CHECKDB 检查仍旧存在错误,可以使用DBCC CHECKTABLE来修复。

set @dbname='需要修复的数据库实体的名称'

use 需要修复的数据库实体的名称

exec sp_dboption @dbname,'single user','true'

declare @dbname varchar(255)

dbcc checktable('需要修复的数据表的名称',REPAIR_ALLOW_DATA_LOSS)

set @dbname='需要修复的数据库实体的名称'

dbcc checktable('需要修复的数据表的名称',REPAIR_REBUILD)

exec sp_dboption @dbname,'single user','true'

------把’ 需要修复的数据表的名称’更改为执行DBCC CHECKDB时报错的数据表的名称

dbcc checktable('需要修复的数据表的名称',REPAIR_ALLOW_DATA_LOSS)

exec sp_dboption @dbname,'single user','false'

dbcc checktable('需要修复的数据表的名称',REPAIR_REBUILD)

  1. 其他的一些常用的修复命令

------把’ 需要修复的数据表的名称’更改为执行DBCC CHECKDB时报错的数据表的名称

DBCC DBREINDEX 重建指定数据库中表的一个或多个索引

exec sp_dboption @dbname,'single user','false'

用法:DBCC DBREINDEX (表名,’’) 修复此表所有的索引。

 

还有其他很多修复命令,诸如DBCC CHECKIDENT 等等,用到的概率比较小,大家可以在ms sql server 的 联机丛书中检索一下。

  1. 其他的一些常用的修复命令

DBCC DBREINDEX 重建指定数据库中表的一个或多个索引

用法:DBCC DBREINDEX (表名,’’) 修复此表所有的索引。 

还有其他很多修复命令,诸如DBCC CHECKIDENT 等等,用到的概率比较小,大家可以在ms sql server 的 联机丛书中检索一下。

本文由9159.com发布于www.9159.com,转载请注明出处:"当数据库出现页损坏或校验和出错时如何处理,

关键词: