DBCC CHECKDB Fails – Troubleshooting DBCC CHECKDB Failure After SQL Server 2017 Restore

backupdbcc-checkdbmigrationsql serversql-server-2017

I have a DB on SQL Server 2014 and after I restore a backup on SQL Server 2017 I get integrity check errors when running CHECKDB. When I restore it on another machine with SQL Server 2014 it works without CHECKDB reporting errors. I need to migrate the database to newer servers, so I can still update things as I see fit.

CHECKDB errors:

Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1737) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1738) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1739) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1740) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1741) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1742) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1743) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
CHECKDB found 7 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 7 allocation errors and 0 consistency errors in database 'DBNAME'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DBNAME).

Table view of errors

Best Answer

You can find out what object(s) those pages belong to by running:

SELECT s.name, o.name, a.index_id
FROM sys.schemas AS s
INNER JOIN sys.objects AS o
ON s.[schema_id] = o.[schema_id]
CROSS APPLY sys.dm_db_database_page_allocations
  (DB_ID(N'DBNAME'),o.[object_id],NULL,NULL,N'LIMITED') AS a
WHERE allocated_page_page_id BETWEEN 1737 AND 1743;

You can try rebuilding that index (or table if index_id is 0 or null) to see if that fixes the allocation problem. If it remains, you could try dropping the index and re-creating it or, again, if it is a table or heap, selecting everything into a new table, creating the new indexes there, dropping the old table, and renaming the new one.

If that doesn't help, you can inspect the contents of those pages, and determine if it is acceptable to lose the data as a part of your upgrade (or, if not, to not upgrade).

DBCC TRACEON(3604,-1);
DBCC PAGE(N'DBNAME', 1, 1737, 2);
DBCC PAGE(N'DBNAME', 1, 1738, 2);
DBCC PAGE(N'DBNAME', 1, 1739, 2);
...

If it is an acceptable loss then you can run the repair with allow data loss as suggested (it might be smart to keep a copy of the backup before repairing in case you ever need to pull the data from those pages in some way).

Related Question