The Database ID 30, Page (1:1141), slot 71 for LOB data type node does not exist.

发布于 2019-09-26 作者 风铃 62次 浏览 版块 前端

今天一大早干活,就发现这个严重问题,很郁闷。

详细的Exception: 



The Database ID 30, Page (1:1141), slot 71 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.



搜索很多文章后,终于搞定,方法如下:



DBCC checkdb('eshoubao')


使用上面的命令后,结果如下:



DBCC results for 'eshoubao'.

DBCC results for 'ScheduleHistory'.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 1749581271, index ID 1, partition ID 72057594044481536, alloc unit ID 72057594049658880 (type LOB data). The off-row data node at page (1:1141), slot 70, text ID 6670647296 does not match its reference from page (1:1141), slot 71.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 1749581271, index ID 1, partition ID 72057594044481536, alloc unit ID 72057594049658880 (type LOB data). The off-row data node at page (1:1141), slot 71, text ID 6653870080 does not match its reference from page (1:1325), slot 31.
Msg 8929, Level 16, State 1, Line 1
Object ID 1749581271, index ID 1, partition ID 72057594044481536, alloc unit ID 72057594049724416 (type In-row data): Errors found in off-row data with ID 6670647296 owned by data record identified by RID = (1:1325:31)
There are 677 rows in 15 pages for object "ScheduleHistory".
CHECKDB found 0 allocation errors and 3 consistency errors in table 'ScheduleHistory' (object ID 1749581271).

CHECKDB found 0 allocation errors and 3 consistency errors in database 'eshoubao'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (eshoubao).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



可以看见,是表ScheduleHistory出了问题,在经过详细排查后,发现出问题的记录在今年5月份。 该表一共有14w多的记录,幸亏这些记录都是日志,可以完全删除。


使用下面的命令修复:



ALTER DATABASE eshoubao
SET single_user WITH ROLLBACK IMMEDIATE;
go
DBCC checkdb ('eshoubao', repair_allow_data_loss);
go


修复结果:



Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
DBCC results for 'eshoubao'.

DBCC results for 'ScheduleHistory'.
Repair: The Clustered index successfully rebuilt for the object "dbo.ScheduleHistory" in database "eshoubao".
Repair: Deleted off-row data column with ID 6670647296, for object ID 1749581271, index ID 1, partition ID 72057594044481536, alloc unit ID 72057594049658880 (type LOB data) on page (1:1141), slot 70.
Repair: Deleted off-row data column with ID 6653870080, for object ID 1749581271, index ID 1, partition ID 72057594044481536, alloc unit ID 72057594049658880 (type LOB data) on page (1:1141), slot 71.
Repair: Deleted record for object ID 1749581271, index ID 1, partition ID 72057594044481536, alloc unit ID 72057594049724416 (type In-row data), on page (1:1325), slot 31. Indexes will be rebuilt.
Repair: The Nonclustered index successfully rebuilt for the object "dbo.ScheduleHistory, IX_ScheduleHistory_NextStart" in database "eshoubao".
Repair: The Nonclustered index successfully rebuilt for the object "dbo.ScheduleHistory, IX_ScheduleHistory_StartDate" in database "eshoubao".
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1749581271, index ID 1 will be rebuilt.
The error has been repaired.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 1749581271, index ID 1, partition ID 72057594044481536, alloc unit ID 72057594049658880 (type LOB data). The off-row data node at page (1:1141), slot 70, text ID 6670647296 does not match its reference from page (1:1141), slot 71.
Could not repair this error.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 1749581271, index ID 1, partition ID 72057594044481536, alloc unit ID 72057594049658880 (type LOB data). The off-row data node at page (1:1141), slot 71, text ID 6653870080 does not match its reference from page (1:1325), slot 31.
Could not repair this error.
Msg 8929, Level 16, State 1, Line 1
Object ID 1749581271, index ID 1, partition ID 72057594044481536, alloc unit ID 72057594049724416 (type In-row data): Errors found in off-row data with ID 6670647296 owned by data record identified by RID = (1:1325:31)
The error has been repaired.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1749581271, index ID 2 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1749581271, index ID 3 will be rebuilt.
The error has been repaired.
There are 696 rows in 15 pages for object "ScheduleHistory".
CHECKDB found 0 allocation errors and 3 consistency errors in table 'ScheduleHistory' (object ID 1749581271).
CHECKDB fixed 0 allocation errors and 1 consistency errors in table 'ScheduleHistory' (object ID 1749581271).

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (eshoubao, repair_allow_data_loss).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



你会发现,问题并没有完全解决,考虑到这表的记录可以完全删除,所以继续使用:



TRUNCATE TABLE ScheduleHistory


之后,再次:



DBCC checkdb('eshoubao')


发现问题已经解决:



CHECKDB found 0 allocation errors and 0 consistency errors in database 'eshoubao'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



最后,切记:(修改database为多用户模式。)



ALTER DATABASE eshoubao SET MULTI_USER WITH NO_WAIT



幸运的是这表的记录可以删除,要是不能删除,该如何修复呢? 请指教。



思前想后这个问题可能出现的情况:


1),硬件问题。


2),转移(backup/restore)过程出现问题, - 这个是最有可能的,因为这个数据已经被转移好几次。  


  


参考文章:


http://support.microsoft.com/kb/2015760


http://www.sqlhacks.com/FAQs/DBCC-REPAIR_ALLOW_DATA_LOSS


SQL Server version:  SQL server 2008 SP1


http://www.kodyaz.com/articles/alter-single-user-multi-user-mode.aspx



其他参考文章:


http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53072


http://blogs.msdn.com/b/psssql/archive/2008/07/15/supporting-sql-server-2008-the-system-health-session.aspx  


收藏
暂无回复