By: Percy Reyes | Updated: 2014-08-19 | Comments (5) | Related: > Database Console Commands DBCCs
Problem
When we are querying SQL Server tables with/without the NOLOCK hint we may get a page corruption error like this: "Could not continue scan with NOLOCK due to data movement". Today we will learn how to deal with this issue.
Solution
In my particular case the error was raised in the Distribution database and was during the execution of a SELECT operation with a NOLOCK hint and then I verified the same error without NOLOCK as well, this error is because one or more data pages are missing at the current position of the scanning with/without NOLOCK locking hint so we can say we have corruption issue in the database. The root cause of this error is because pages were deleted or moved by page splits caused by DML statements so that SQL Server is not able to continue scan operation.
In order to verify that it is a page corruption issue you can run the DBCC CHECKDB command with PHYSICAL_ONLY option.
DBCC CHECKDB(MY_DATABASE) WITH PHYSICAL_ONLY
As a result, you may see many errors:
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 (type Unknown), page ID (1:219368) contains an incorrect page ID in its page header. The PageId in the page header = (5385:-1048674823). Msg 8939, Level 16, State 98, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 6989691077213028352 (type Unknown), page (29184:1694528512). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -14. Msg 8909, Level 16, State 1, Line 1
From the output above we can validate for example that the allocation unit 144115188075921408 does not exist so this is the reason why “data pages are missing” and “(type Unknown)” appear in each message. Well, at this point we know it is a critical issue and now I will show more messages returned by the DBCC command:
Msg 8928, Level 16, State 1, Line 1 Object ID 501576825, index ID 1, partition ID 72057594040549376, alloc unit ID 72057594044940288 (type In-row data): Page (1:1408252) could not be processed. Msg 8928, Level 16, State 1, Line 1 Object ID 501576825, index ID 1, partition ID 72057594040549376, alloc unit ID 72057594044940288 (type In-row data): Page (1:1408253) could not be processed. Msg 8928, Level 16, State 1, Line 1 Object ID 501576825, index ID 1, partition ID 72057594040549376, alloc unit ID 72057594044940288 (type In-row data): Page (1:1408254) could not be processed. Msg 8928, Level 16, State 1, Line 1 Object ID 501576825, index ID 1, partition ID 72057594040549376, alloc unit ID 72057594044940288 (type In-row data): Page (1:1408255) could not be processed. CHECKDB found 0 allocation errors and 112 consistency errors in table 'MSrepl_commands' (object ID 501576825). CHECKDB found 0 allocation errors and 176 consistency errors in database 'distribution'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (distribution). DBCC execution completed. If DBCC printed error messages, contact your system administrator.
We can see now above that messages don’t contain “(type Unknown)” that means the allocations exist in the database, but they are damaged and they belong to MSrepl_commands table (from the distribution database). However, there are cases where you don’t know what is the object and to fix it we will need to know the table/index where these allocations belong, for this situations the following steps can help us.
Sometimes the error messages will not give us both allocation_unit_id and partition_id values, then we should get the partition_id filtering by allocation_unit_id from sys.allocation_units view:
SELECT partition_id FROM sys.allocation_units WHERE allocation_unit_id= 72057594044940288
After this we should look for object name in sys.partitions view by using partition_id as follow:
SELECT object_name(object_id) as object_name FROM sys.partitions WHERE partition_id=72057594040549376
For this case, the previous query indicated that this table is MSrepl_commands, with all of this, the next step is to fix all objects damaged. One way to fix it is by trying to REBUILD the clustered index:
ALTER INDEX ucMSrepl_commands ON MSrepl_commands REBUILD;
and trying that another error like this can appear:
Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:219359; actual 0:0). It occurred during a read of page (1:219359) in database ID 8 at offset 0x0000006b1be000 in file 'S:\SQLDATA\distribution.MDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
The error message indicates that there is an error during a READ operation. We have also verified that we could not fix the issue by rebuilding the clustered index. Now we don’t have any option than try with DBCC CHECKTABLE using REPAIR_REBUILD option if we have only missing rows.
USE [master] GO ALTER DATABASE [distribution] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO USE [distribution] GO DBCC CHECKTABLE(MSrepl_commands, 'REPAIR_REBUILD') GO ALTER DATABASE [distribution] SET MULTI_USER WITH ROLLBACK IMMEDIATE
and we have verified above the MSrepl_commands table has also pages damaged then we can try with REPAIR_ALLOW_DATA_LOSS option. Use it as a last option because this option will try to repair the database at the cost of some data loss.
USE [master] GO ALTER DATABASE [distribution] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO USE [distribution] GO DBCC CHECKTABLE(MSrepl_commands, 'REPAIR_ALLOW_DATA_LOSS') GO ALTER DATABASE [distribution] SET MULTI_USER WITH ROLLBACK IMMEDIATE
The execution will try to fix damaged pages and you will see in the output message that there no more errors like showed above. Now it is important to note that DBCC CHECKTABLE or DBCC CHECKDB must be run regularly against your database. The frequency to run it depends directly on your business and the size of your database, because this can affect the performance of your server.
Next Steps
- To be aware about impact of DBCC execution with advanced options, read these additional article related
- Check out these DBCC Tips.
About the author
Percy Reyes is a SQL Server MVP and Sr. Database Administrator focused on SQL Server Internals with over 10+ years of extensive experience managing critical database servers.
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-08-19
