Error: Could not continue scan with NOLOCK due to SQL Server data movement

Ran  the following

DBCC CHECKDB(MY_DATABASE) WITH PHYSICAL_ONLY
I got table error something like below

Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown),
page ID (1:219356) contains an incorrect page ID in its page header.
The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 144115188075921408

So I ran the following

USE [master]
GO
ALTER DATABASE [MY_DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [DATABASE]
GO
DBCC CHECKTABLE(MY-Badtable, 'REPAIR_REBUILD')
GO
ALTER DATABASE [MY_DATABASE] SET MULTI_USER WITH ROLLBACK IMMEDIATE

Then  ran the following again
DBCC CHECKDB(MY_DATABASE)


This worked, no errors!

suspect pages errors

USE msdb

GO

SELECT * FROM dbo.suspect_pages


Event_type column gives different values

Errors Recorded in suspect_pages Table
The suspect_pages table contains one row per page that failed with an 824 error, up to a limit of 1,000 rows. The following table shows errors logged in the event_type column of the suspect_pages table.

Error description	event_type value
823 error caused by an operating system CRC error or 824 error other than a bad checksum or a torn page (for example, a bad page ID)	1
Bad checksum	2
Torn page	3
Restored (The page was restored after it was marked bad)	4
Repaired (DBCC, AlwaysOn, or mirroring repaired the page)	5
Deallocated by DBCC	7

The suspect_pages table also records transient errors. Sources of transient errors include an I/O error (for example, a cable was disconnected) or a page that temporarily fails a repeated checksum test.

How the Database Engine Updates the suspect_pages Table
The Database Engine takes the following actions on the suspect_pages table:

• If the table is not full, it is updated for every 824 error, to indicate that an error has occurred, and the error counter is incremented. If a page has an error after it is fixed by being repaired, restored, or deallocated, its number_of_errors count is incremented and its last_update column is updated
• After a listed page is fixed by a restore or a repair operation, the operation updates the suspect_pages row to indicate that the page is repaired (event_type = 5) or restored (event_type = 4).
• If a DBCC check is run, the check marks any error-free pages as repaired (event_type = 5) or deallocated (event_type = 7).

---To clean up the suspect_pages table run the following

delete s

from msdb.dbo.suspect_pages s

where event_type in (4,5,7)

suspect mode: database

---once the database goes to suspect mode
--follow the steps to recover the database

--1. reset the database from suspect mode
--sp_resetstatus turns off the suspect flag on a database.
--This procedure updates the mode and status columns of the named database in sys.databases.
--Also note that only logins having sysadmin priveleges can perform this :


use master
GO
exec sp_resetstatus 'db1';
--2.set the database to emergency mode
--Once the database is set to EMERGENCY mode it becomes a READ_ONLY copy
--only members of sysadmin fixed server roles have privileges to access it.
--The basic purpose for this is to facilitate troubleshooting

alter database db1 set emergency;

--3.run the consistency test
dbcc checkdb(db1)

--4. Roll the databse to single user mode
use master
GO
ALTER DATABASE db1 SET SINGLE_USER
WITH ROLLBACK IMMEDIATE


--5. this query will attempt to repir all the reported loss. This query can cause some data loss
dbcc checkdb('db1',REPAIR_ALLOW_DATA_LOSS)

--6.finally bring the database ONLINE and set to multiuser mode
ALTER DATABASE db1 SET MULTI_USER
WITH ROLLBACK IMMEDIATE