SQL Server日志损坏造成整个数据库损坏的修复

SQL Server 日志损坏造成整个数据库损坏的修复

版本：V1.0 作者：知行合一

邮箱：409629442@qq.com

时间：2014/9/29

一、 问题说明

由于用户的数据库某张表比较大，大约有1000万条记录，数据库管理员在非业务时间对这个表进行删除清理，删除操作持续了1个小时左右，到工作时间，仍然没有正常结束。前端用户反应应用系统比较慢后，数据库管理员对删除操作进行终止，终止时仍然无法正常终止。

最后，数据库管理员不得已对数据库进行重启，重启后发现数据库已经无法正常打开。由于用户比较急，就用一个比较老的备份进行了恢复。我们赶赴现场后，原先的数据库已经被删除，但用户已经针对原先的数据库文件和日志文件进行了拷贝。我们把备份的数据库文件拷贝到异机进行附加，总是报错，提示无法读取日志文件。具体报错如下：

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Msg 1813, Level 16, State 2, Line 2

通过上面的报错可知，数据库的日志文件发生了损坏，这时已经不能通过简单的附加方式进行恢复，也无法通过无日志附加的方式进行附加，因为这时数据库处在一个不一致的状态。

二、 环境介绍

操作系统：Windows 2008 R2 SQL server: SQL Server 2008 SP1 数据文件路径：

数据文件路径 D:\\PPmonitor \\PPmonitor_Data.MDF D:\\PPmonitor\\PPmonitor_Log.LDF D:\\PPmonitor\\PPmonitor_Data1.NDF D:\\PPmonitor\\PPmonitor_Log1.ldf D:\\PPmonitor\\PPmonitor_Log2.ldf G:\\PPmonitor\\PPmonitor_Dtat3.Mdf G:\\PPmonitor\\PPmonitor_data2.Ndf G:\\PPmonitor\\PPmonitor_Log3.Ldf File_id 1 2 3 4 5 6 7 8 逻辑文件名称 PPmonitor_Data PPmonitor_Log PPmonitor_1_Data PPmonitor_1_Log PPmonitor_2_Log PPmonitor_3_Data PPmonitor_2_Data PPmonitor_3_Log 文件组 PRIMARY PRIMARY PRIMARY PRIMARY 大小 41787200KB 注：数据文件后面为数据文件的file_id,file_id 可以通过sys.master_files进行查看

三、处理过程

由于这时数据库已经无法正常打开（已经没有对应的数据库，或数据库状态错误，查看不了任何属性信息），所以我们必须重新创建同名的数据库，然后用备份的数据文件覆盖新创建的数据文件。处理的主要过程如下：

3.1、新建同名的数据库。

1、新建数据库时，要求数据文件目录、路径、数据文件大小要和原来保持一致。如果是恢复到其他机器，要保证SQL Server实例的版本和原来保持一致，小版本也要保持一致，如果小版本不一致，需要设置实例和数据库兼容级别。

2、数据文件大小不能小于原来的数据文件，可以比原来的数据文件大。数据文件大小不一致时，替换数据文件时会发生错误。

3、日志文件大小可以不一致。

4、需要保持文件的file_id 要和原来对应，文件的Id 可以通过mster数据库系统表sys.master_files进行查看。如果包含文件组，且文件组中含有多个数据文件，直接一起创建的时候，可能会产生文件id和原来不一致的情况，这时可以采用一个一个数据文件单独创建的方式进行创建。如果文件id 不一致，用备份的数据文件替换新建的数据文

件时会报错。具体错误信息如下：

文件Id 的查看方法： Use master

Select * from sys.master_file;

3.2、用备份的数据文件替换新的数据文件

1、首先停止SQL Server服务 2、替换数据文件和日志文件 3、启动数据库服务

如果替换时，报拒绝访问错误，需要把数据文件的权限更改为everyone 完全访问

3.3、数据库日志修复

在用备份的数据和日志文件替换新建的数据文件和日志文件后，启动数据库时，数据库自动置为in recovery 状态，这时修复的时间可能会比较长。

由于日志文件3已经发生损坏，修复到损坏日志部分的时候，数据库会无法进行修复。这时可以采用如下方法处理：

sp_configure 'allow updates', 1 reconfigure with override Go

3.4、损坏的数据库日志重建

1、停止数据库服务

2、删除日志文件3的数据文件

3、启动数据库

4、查询数据库信息

select name,filename from sys.sysaltfiles where dbid=5;

select * from sys.master_files;

5、重建损坏的日志文件

如果数据库没有处在质疑模式，可能需要手工把数据库设置为质疑模式：

sp_configure 'allow updates', 0 reconfigure with override Go

ALTER DATABASE PPmonitor SET EMERGENCY GO

ALTER DATABASE PPmonitor SET SINGLE_USER;

alter database PPmonitor rebuild log

on(Name=PPmonitor_3_Log,filename='G:\\PPmonitor\\PPmonitor_Log3.Ldf');

6、alter database PPmonitor set online;

7、alter database PPmonitor set multi_user go

这时数据库中的表已经能够正常访问，由于某些表仍然存在一致性问题，在访问时会报错，可能的报错信息如下：

3.5、数据库的一致性修复

1、把数据库置为紧急和单用户模式

reconfigure with override Go

ALTER DATABASE PPmonitor SET EMERGENCY GO

ALTER DATABASE PPmonitor SET SINGLE_USER;

2、修复数据库中存在的错误

DBCC CheckDB (PPmonitor, REPAIR_ALLOW_DATA_LOSS)

修复完成后，有部分报错，具体如下： sg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed. Msg 2510, Level 16, State 17, Line 1

DBCC checkdb error: This system table index cannot be recreated.

DBCC results for 'PPmonitor'.

Repair: The page (1:3960704) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID

72057594284408832 (type Unknown).

Repair: The page (1:4088798) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID

72057594284408832 (type Unknown).

Repair: The page (1:4089144) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID

72057594284408832 (type Unknown). ……………………………………………………………. ……………………………………………………………….

CHECKDB found 12638 allocation errors and 0 consistency errors in table 'BarcodeReslut' (object ID 1966682104).

CHECKDB fixed 12638 allocation errors and 0 consistency errors in table 'BarcodeReslut' (object ID 1966682104).

Repair: IAM chain for object ID 2000166321, index ID 1, partition ID 72057594193575936, alloc unit ID 72057594196459520

(type In-row data), has been truncated before page (1:210) and will be rebuilt.

Repair: The extent (6:448) has been allocated to object ID 2000166321, index ID 1, partition ID 72057594193575936, alloc

unit ID 72057594196459520 (type In-row data).

Repair: Page (7:4613336) next and (0:0) previous pointers have been set to match each other in object ID 2000166321,

index ID 1, partition ID 72057594193575936, alloc unit ID 72057594196459520 (type In-row data).

Msg 2575, Level 16, State 1, Line 1

The Index Allocation Map (IAM) page (1:210) is pointed to by the next pointer of IAM page (7:4613336) in object ID

2000166321, index ID 1, partition ID 72057594193575936, alloc unit ID 72057594196459520 (type In-row data), but it was

not detected in the scan.

The error has been repaired.

CHECKDB found 1 allocation errors and 0 consistency errors in table 'TestVoltage' (object ID 2000166321).

CHECKDB fixed 1 allocation errors and 0 consistency errors in table 'TestVoltage' (object ID 2000166321).

Msg 8913, Level 16, State 3, Line 1

Extent (7:616) is allocated to 'dbo.ZModul' and at least one other object. The error has been repaired.

CHECKDB found 1 allocation errors and 0 consistency errors in table 'ZModul' (object ID 2121110647).

CHECKDB fixed 1 allocation errors and 0 consistency errors in table 'ZModul' (object ID 2121110647).

CHECKDB found 470285 allocation errors and 9 consistency errors in database 'PPmonitor'. CHECKDB fixed 470259 allocation errors and 8 consistency errors in database 'PPmonitor'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PPmonitor,

repair_allow_data_loss).

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

3、表分配检查

USE [PPmonitor]; DBCC CheckAlloc();

DBCC results for 'PPmonitor'.

Msg 8904, Level 16, State 1, Line 2

Extent (6:525720) in database ID 5 is allocated by more than one allocation object. Msg 8913, Level 16, State 1, Line 2

Extent (6:525720) is allocated to 'GAM' and at least one other object. Msg 8904, Level 16, State 1, Line 2

Extent (6:582336) in database ID 5 is allocated by more than one allocation object. Msg 8913, Level 16, State 1, Line 2

Extent (6:582336) is allocated to 'GAM' and at least one other object. Msg 8904, Level 16, State 1, Line 2

Extent (6:606600) in database ID 5 is allocated by more than one allocation object. Msg 8913, Level 16, State 1, Line 2

Extent (6:606600) is allocated to 'GAM' and at least one other object. ……………………………………………………………. ……………………………………………………………..

data), index extents 2089, pages 16491, mixed extent pages 6.

Object ID 2121110647, index ID 10, partition ID 72057594202947584, alloc unit ID 72057594205765632 (type In-row data), index extents 945, pages 7403, mixed extent pages 15. Object ID 2138594807, index ID 1, partition ID 72057594173194240, alloc unit ID 72057594175946752 (type In-row data), data extents 0, pages 3, mixed extent pages 3.

The total number of extents = 1938384, used pages = 15430706, and reserved pages = 15507106

in this database.

(number of mixed extents = 702, mixed pages = 5650) in this database.

CHECKALLOC found 58 allocation errors and 0 consistency errors in database 'PPmonitor'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

4、查询测试

查询指定条件时报错，只能全部按顺序扫描，即只能使用 select * from table

使用select count(*) from table 报错，具体报错如下： Attempt to fetch logical page (1:229734) in database 5 failed. It belongs to allocation unit 72057594251182080 not to 72057594275561472. 消息 605，级别 21，状态 3，第 2 行

尝试在数据库 5 中提取逻辑页 (1:1824) 失败。该逻辑页属于分配单元 72057594194165760，而非

72057594237091840。

5、表无损修复

把数据启动到单用户模式下

ALTER DATABASE PPmonitor SET single_user; GO

对出错的表进行修复

DBCC CheckTable(AdjustData_7878_N, REPAIR_REBUILD);

AdjustData_7878_N的 DBCC 结果。

修复: 已为数据库 'PPmonitor' 中的对象 'dbo.AdjustData_7878_N' 成功地重新生成了 Clustered 索引。 消息 8945，级别 16，状态 1，第 1 行

表错误: 将重新生成对象 ID 368876531，索引 ID 1。

该错误已修复。

消息 8976，级别 16，状态 1，第 1 行

表错误: 对象 ID 368876531，索引 ID 1，分区 ID 72057594241220608，分配单元 ID 72057594244825088 (类型为 In-row data)。在扫描过程中未发现页 (3:656)，但该页的父级 (7:4810173) 和上一页 (7:4850791) 都引用了它。请检查以前的错误消息。

该错误已修复。

消息 8980，级别 16，状态 1，第 1 行

表错误: 对象 ID 368876531，索引 ID 1，分区 ID 72057594241220608，分配单元 ID 72057594244825088 (类型为 In-row data)。索引节点页 (7:4810173)，槽 617 指向子页 (3:657) 和上一子页 (3:656)，但未遇到这些页。

该错误已修复。

消息 8980，级别 16，状态 1，第 1 行

表错误: 对象 ID 368876531，索引 ID 1，分区 ID 72057594241220608，分配单元 ID 72057594244825088 (类型为 In-row data)。索引节点页 (7:4810173)，槽 618 指向子页 (3:658) 和上一子页 (3:657)，但未遇到这些页。

该错误已修复。

消息 8980，级别 16，状态 1，第 1 行

表错误: 对象 ID 368876531，索引 ID 1，分区 ID 72057594241220608，分配单元 ID 72057594244825088 (类型为 In-row data)。索引节点页 (7:4810173)，槽 619 指向子页 (3:659) 和上一子页 (3:658)，但未遇到这些页。

该错误已修复。

消息 8980，级别 16，状态 1，第 1 行

表错误: 对象 ID 368876531，索引 ID 1，分区 ID 72057594241220608，分配单元 ID 72057594244825088 (类型为 In-row data)。索引节点页 (7:4810173)，槽 620 指向子页 (3:660) 和上一子页 (3:659)，但未遇到这些页。

该错误已修复。

对象 'AdjustData_7878_N' 的 59527 页中有 344840 行。

CHECKTABLE 在表 'AdjustData_7878_N' (对象 ID 368876531)中发现 0 个分配错误和 5 个一致性错误。 CHECKTABLE 在表 'AdjustData_7878_N' (对象 ID 368876531)中修复了 0 个分配错误和 5 个一致性错误。

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

查询表的分配单元可以通过如下语句：

SELECT au.allocation_unit_id, OBJECT_NAME(p.object_id) AS table_name, fg.name AS filegroup_name, au.type_desc AS allocation_type, au.data_pages, partition_number FROM sys.allocation_units AS au

JOIN sys.partitions AS p ON au.container_id = p.partition_id JOIN sys.filegroups AS fg ON fg.data_space_id = au.data_space_id

WHERE au.allocation_unit_id = 72057594194165760 OR au.allocation_unit_id = 72057594194165760 ORDER BY au.allocation_unit_id; GO

查询表的object id 可以通过如下语句：

select name from sysobjects where id = '716021782'; select * from sysobjects where type = 'U';

select id from sysobjects where name='ReView2Reslut_09';

USE master; GO

SELECT OBJECT_ID(N'PPmonitor.dbo.ReView2Reslut_09') AS 'Object ID'; GO

select ID from sysobjects where name='AdjustData_7053B' and type='U';

检查page分配：

DBCC TRACEON (3604); GO

DBCC PAGE (PPmonitor,3,1410586,1);

6、表有损修复

由于查询时，仍然报错，这时只能执行有损修复

DBCC CheckTable(AdjustData_7053B, repair_allow_data_loss);

7、查询测试

再次查询，表查询正常

8、重建索引（根据需要进行索引重建）

ALTER TABLE [dbo].[AdjustData_7053B] ADD CONSTRAINT [PK_AdjustData_7053B] PRIMARY KEY CLUSTERED (

[AdjustDataID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

至此数据库修复完毕，数据正常恢复到出问题的时间点，基本上没有发生数据丢失。

本文来源：https://www.bwwdw.com/article/5gxv.html