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

图片 14

五. 获取要修复的数据页面 

-- 使用checkdb检查
DBCC CHECKDB(BackupPageTest)

  通过校验,提示无法处理面(1:89)如下图

  图片 1

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

1.确定损坏(使用DBCC CHECKDB)
2.确定损坏的对象及对象类型(如索引页、分配页等)
3.确定适合的修复方法

三. 备份

  为了演示损坏的数据页面,新建一个PageTest表,初始化三个PAGE页,后面人为的破坏一个数据页面。

use BackupPageTest
-- 创建表
create table PageTest
(
    ID int,
    name varchar(8000)
)
-- 产生
insert into PageTest
select 1, REPLICATE('a',8000)
insert into PageTest
select 1, REPLICATE('b',8000)
insert into PageTest
select 1, REPLICATE('c',8000)

 sys.system_internals_allocation_units 查看分配页情况

 图片 3

/* 
第1个参数:库名
第2个参数:表名
第3个参数:-1: 显示所有IAM、数据分页、及指定对象上全部索引的索引分页
PageFID: 文件ID
PageType=1 指数据页面
PageType=10 IAM页面
*/ 
-- 未公开的命令,语法如下:
DBCC IND(dbname,tablename,-1)

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

现在按照下面的步骤:
现在我们已经知道哪些是管理区分配页,接下来依然按三个步骤进行:

  图片 4

--更新第三条数据,结果报错
update PageTest set  id=2  where ID is null

  图片 5

-- 插入第4条是成功的
insert into PageTest
select 4, REPLICATE('d',8000)

  图片 6

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’.

页可用空间
(PFS):记录每页的分配状态,是否已分配单个页以及每页的可用空间量,每64MB会有一个PFS页,它的ID总是为1,之后每8,088页出现一次。
       Page ID = 1 or Page ID % 8088

二.还原步骤      

  (1) 获取要还原的损坏页面的页ID,当sql
server遇到校验或残缺写错误时,会返回页面编号。可以通过查询msdb数据库里的suspect_pages表,或者监视事件和errorlog文件里记录的错误信息,查找到损坏的页面ID。
  (2)
从包含页的完整数据库备份,文件备份或文件组备份开始进行页面还原。在restore
database 语句中,使用page子句列出所有要还原的页ID。
  (3) 应用最近的差异备份。
  (4) 应用后续的日志备份。
  (5) 创建新的数据库尾日志备份。
  (6) 还原新的尾日志备份,应用这个新的日志备份后,就完成了页面还原。

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

全局分配映射表
(GAM):记录已分配的区,对于一个数据文件,每4GB会有一个GAM页,它的ID总是为2,之后每511,232页出现一次。
        Page ID = 2 or Page ID % 511232

  图片 7

use master
-- 完整备份
backup database  BackupPageTest to BackupTestDevice

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

确定损坏
首先,请先下载一个PFS损坏的数据库,然后运行DBCC CHECKDB:

四 模拟页面损坏

  使用PagePID为89的数据页面进行演示,通过dbcc
page查看该页面,知道该页数据是存储的第三条数据。

dbcc traceon (3604)
dbcc page('BackupPageTest',1,89,1)

  图片 8

  使用 dbcc wirtepage来模拟该面损坏:

-- 未公开的命令语法为如下
dbcc writepage ({ dbid, 'dbname' }, fileid, pageid, offset, length, data)

-- 模拟页面损坏
dbcc writepage(BackupPageTest,1,89,96,10,0x65656565656565656565)

图片 9

-- 查询该表时,第三条数据显示NULL
select * from PageTest

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

Declare @DBCC Table (
    Error int,
    Level smallint,
    State tinyint,
    MessageText varchar(2500),
    RepairLevel varchar(30) null,
    Status tinyint,
    DbId int,
    DbFragId int,
    ObjectId int,
    IndexId int,
    PartitionId bigint,
    AllocUnitId bigint,
    RidDbId int,
    RidPruId int,
    [File] int,
    Page int,
    Slot int,
    RefDbId int,
    RefPruId int,
    RefFile int,
    RefPage int,
    RefSlot int,
    Allocation bigint)

Insert Into @DBCC
Exec sp_executesql N'DBCC CheckDB(PFSCorruption)
    With No_InfoMsgs, All_ErrorMsgs, TableResults;';

Select Level,
    State,
    MessageText,
    RepairLevel,
    ObjectId,
    IndexId,
    [File],
    Page,
    RefFile,
    RefPage
From @DBCC;

一.概述

  当数据库发生损坏,数据库的每个文件都能打开,只是其中的一些页面坏了,这种情况可以借助DBCC
CHECKDB进行数据库检查修复。如果要保证数据库不丢失,或修复不好,管理员只能做数据库完整恢复,为了少数页面恢复整个数据库,代价是比较高的,sql
server引入了页面还原功能,可以指定还原若干页面,从而能够大大节省数据库恢复时间。
  页面还原用于修复隔离的损坏页面,还原恢复时间比文件更快,减少了还原过程中处于离线的数据量,当某个文件的大量页面都出现损坏,可以直接还原该文件(需要有文件备份)。要进行还原的页面是在访问该页面,遇到错误而标记为”可疑”,可以试试去找msdb.dbo.suspect_pages表。在页面还原后,也需要恢复所有的日志文件备份
  1.1 还原的限制,不能还原的页
    (1)事务日志不能还原。
    (2)分配页面:全局分配映射GAM页面,共享全局分配映射SGAM页面和可用空间PFS页面,这些系统页面损坏,页面还原无法恢复。
    (3)所有数据文件的页面0 的(文件启动页面)。
    (4)页面1:9的(数据库启动页面)。
  1.2 还原条件
    (1) 必需使用完整恢复模式。
    (2) 只读文件组中的页面无法还原。
    (3) 还原顺序必须是从完整备份,文件备份中恢复页面开始。
    (4) 页面还原需要截止到当前日志文件的连续日志备份
    (5) 数据库备份和页面还原不能同时进行。

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

图片 10

六. 还原

use master
--从完整数据库备份,开始还原,指定要还原的PAGE页
restore database BackupPageTest page='1:89' from BackupTestDevice with file=39,  norecovery
--创建新的尾日志备份
backup log BackupPageTest to BackupTestDevice

 
 此时访问数据表PageTest将会发错,如下图所示,表明在还原过程中数据是不可访问的。

 图片 11

图片 12

--最后还原新的尾日志备份
restore log BackupPageTest from BackupTestDevice with file=40,  recovery

   数据修复过来了,如下图:

  图片 13

  再次CHECKDB 检查表状态

  图片 14

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选项永远是最后的选择。

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

 

采用适合的方法
在开头的时候我们已经说过管理区分配页不能被修复或还原,所以我们不得不做整个数据库的还原,那就需要检查备份的情况,尤其是事务日志备份到什么时间,然后开始做还原,如果没有备份,则需要将数据导到一个新建的数据库中。如果你能手动用一个十六进制的编辑器手动修复它(不是NB就是DS),那么最好先copy出一份来做这个操作。 


说明:灾难恢复系列的文章是由 Robert Davis写的,发布在SQLSoldier,
个人认为挺不错的,所以根据自己的理解,边测试边整理,并非直接翻译,如有不准确,欢迎指正。

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图