首页
网站首页
公司简介
资讯中心
推荐内容
返回顶部
一次磁盘IO过高分析过程,SqlServer索引页损坏恢复
发布时间:2019-12-29 03:01
浏览次数:

1、查看监控,发现整点时间有写IO过高情况

declare @total int;
declare @count int;
set @count=0;
SELECT @total=COUNT(1) FROM TB_JJ;

create table #T
(
  rid int PRIMARY KEY,
  sys_guid NVARCHAR(40)
);

INSERT INTO #T SELECT ROW_NUMBER() over (order by sys_guid) as rid,sys_guid FROM TB_JJ;

WHILE @count<@total
BEGIN
    set @count=@count+10000;
    INSERT INTO TB_JJ_Info(sys_guid) SELECT sys_guid FROM #T where rid<=@count;    
    DELETE FROM #T where rid<=@count;
END
DROP TABLE #T;

问题背景

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

使用数据库管理工具插入数据

打开数据库,选则要插入数据的表-》右键点击-》选择插入前200行-》在右边视图中输入要插入的数据(如果字段设置为不为空,则必须输入,如果字段设置为可空,则不必须输入,插入部分、单条数据都可以)-》输入完成后,光标移动到下一行-》如果sqlserver使用默认保存,则光标离开后就会添加成功,如果sqlserver设置自动保存开关关闭,则需要手动执行F5保存数据-》刷新即可显示

图片 1

图片 2

图片 3

报错信息

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

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

使用T-SQL脚本插入数据

语法:insert into table_name values(值1, 值2,....);

插入一条完整数据:insert into [tests].[dbo].[test1] values('张三','男','22','1');

图片 4

语法:insert into table_name(列名1,列名2) values(值1, 值2);

插入指定字段数据:insert into [tests].[dbo].test1(name,sex,classid) values('李四','男','2');

图片 5

语法:insert into table_name(列名1,列名2) values(值1, 值2),(值3 ,值4),(值5 ,值6);

插入多条数据:

insert into [tests].[dbo].test1(name,sex,age,classid)

values('王五','男','22','2'),('马六','男','22','2'),('孙七','男','22','2');

图片 6

语法:insert into table_name1(列名1,列名2) select 列名1,列名2 from table_name2  where 条件;

蠕虫复制两个表结构一模一样,插入时插入主键步骤:

1、打开自增列属性,设置为ON,允许表在插入数据的时候插入指定的自增列。

set identity_insert [tests].[dbo].test3 on;

2、执行插入语句(插入列必须和查询出来的列相对应)。

insert into [tests].[dbo].test3(id,name,sex,age,classid)
select * from [tests].[dbo].test1;

3、插入完成,关闭自增列属性,设置为off

 set identity_insert [tests].[dbo].test3 off ;

图片 7

 

 蠕虫复制两个表结构不一样,选择性插入表中(注意:查询出来的列和要插入的列必须一一对应):

insert into tests.dbo.test2(name)
select name from tests.dbo.test1;

图片 8

示例结果:

图片 9

 

 

报错可能的原因

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

2、iotop 分析确认io高峰是由mysql导致的

5.7号和4.20号的数据量对比

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

图片 10

数据库修复

--修复改数据库 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并未能解决问题。

 

重建索引

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;的形式插入到另外的表,重新创建该表后将数据恢复回来,然后重建索引。

3、开启general log,分析SQL

结语

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

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

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

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

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

 

 

set global general_log = on ;

 

4、观察binlog 与 general log 发现 文件增长量不大,怀疑不是有Insert 与 update 与 delete 导致的 写IO过高

 

5、show full processlist ;发现有慢SQL

*************************** 6. row ***************************

     Id: 337153

   User: user_car_bill

   Host: 192.168.3.10:63018

友情链接: 网站地图
Copyright © 2015-2019 http://www.nflfreepicks.net. 新葡萄京娱乐场网址有限公司 版权所有