[SQL]Msg 601, Could not continue scan with NOLOCK due to data movement.

2013-03-21

4720
0
SQL

昨天同事說在執行SQL時出現以下的錯誤訊息，

Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.

上網查了一下，好像是資料庫有問題，於是執行DBCC CHECKDB(有問題的db)，

果然發現了以下的錯誤，

DBCC results for 'sys.sysrscols'.
Msg 8909, Level 16, State 1, Line 2
Table error: Object ID 3, index ID 1, partition ID 196608, alloc unit ID 196608 (type In-row data), page ID (1:58731) contains an incorrect page ID in its page header. The PageId in the page header = (1:66950).
There are 13656 rows in 164 pages for object "sys.sysrscols".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysrscols' (object ID 3).

DBCC results for 'myTable'.
Msg 8978, Level 16, State 1, Line 2
Table error: Object ID 922199527, index ID 3, partition ID 72057596529541120, alloc unit ID 72057596672933888 (type In-row data). Page (1:10921) is missing a reference from previous page (1:58731). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 2
Object ID 922199527, index ID 3, partition ID 72057596529541120, alloc unit ID 72057596672933888 (type In-row data): Page (1:58731) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 2
Table error: Object ID 922199527, index ID 3, partition ID 72057596529541120, alloc unit ID 72057596672933888 (type In-row data). Page (1:58731) was not seen in the scan although its parent (1:57862) and previous (1:58730) refer to it. Check any previous errors.
There are 1078 rows in 216 pages for object "myTable".
CHECKDB found 0 allocation errors and 3 consistency errors in table 'myTable' (object ID 922199527).

參考「Sql Server Msg 8978 – missing a reference from previous page」看起來是INDEX的問題，所以就查看看是那個INDEX有問題，然後重新建立它，如下，

--Table error: Object ID 922199527, index ID 3,
SELECT * FROM sys.indexes WHERE object_id = 922199527 and index_id = 3

知道是 IDX_VALID_DTE 這個INDEX的問題，所以就DROP掉再重新建立它，如下，

DROP INDEX [IDX_VALID_DTE]
ON [dbo].[myTable]
GO

CREATE NONCLUSTERED INDEX [IDX_VALID_DTE] ON [dbo].[myTable]
(
	[SDATE] ASC,
	[EDATE] ASC,
	[DEP_SERIL_NO] ASC,
	[CMP_SERIL_NO] ASC,
	[DEP_NAME] ASC
) 
GO

重建INDEX後，再執行一次DBCC CHECKDB(有問題的db)，發現已經沒有錯誤了。