7

The restore of our database to new server hardware failed with a page error.

Message
SQL Server detected a logical consistency-based I/O error: 
incorrect pageid (expected 49:8125916; actual 49:29097436).
It occurred during a read of page (49:8125916) in database ID 7 at
offset 0x00000f7fbb8000 in file x:\Databases\Data07\DataWarehouse_OperationalData_15.ndf'.
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.

A DBCC CheckDB will take us about a week on this 70 TB database and 6 years old server.
Is there any chance that it is my backup that is corrupt?
Or that the new server is at fault?
Or is it the production database on the old server that is the problem?

This is SQL Server 2016 SP1 CU1 and page_verify_option_desc is CHECKSUM.

This is what I can see from the error log on the new server:

Starting up database 'DataWarehouse'.
The database 'DataWarehouse' is marked RESTORING and is in a state that does not allow recovery to be run.
Error: 824, Severity: 16, State: 2.
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 49:8125916; actual 49:29097436). It occurred during a read of page (49:8125916) in database ID 7 at offset 0x00000f7fbb8000 in file 'S:\MSSQL\DSA\Databases\DataWarehouse\Data07\DataWarehouse_OperationalData_15.ndf'.  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.
Error: 18456, Severity: 14, State: 38.

When I run this on the old server: 

DBCC TRACEON (3604);
DBCC PAGE ('datawarehouse', 49, 8125916, 0);

I get this

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (49:8125916)


BUFFER:


BUF @0x000000802C5E9300

bpage = 0x0000004DF03E8000          bhash = 0x0000000000000000          bpageno = (49:8125916)
bdbid = 5                           breferences = 1                     bcputicks = 0
bsampleCount = 0                    bUse1 = 5844                        bstat = 0x9
blog = 0x15ab215a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x0000004DF03E8000

m_pageId = (49:8125916)             m_headerVersion = 1                 m_type = 3
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0xa200
m_objId (AllocUnitId.idObj) = 10814647                                   m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72058302786633728                                
Metadata: PartitionId = 72058370492596224                                Metadata: IndexId = 1
Metadata: ObjectId = 1916450641     m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 0                         m_slotCnt = 2                       m_freeCnt = 2452
m_freeData = 5764                   m_reservedCnt = 0                   m_lsn = (2455108:11140830:20)
m_xactReserved = 0                  m_xdesId = (28:447547798)           m_ghostRecCnt = 0
m_tornBits = 31453216               DB Frag ID = 1                      

Allocation Status

GAM (49:7668480) = ALLOCATED        SGAM (49:7668481) = NOT ALLOCATED   
PFS (49:8120352) = 0x42 ALLOCATED  80_PCT_FULL                           DIFF (49:7668486) = CHANGED
ML (49:7668487) = NOT MIN_LOGGED    


DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC CHECKFILEGROUP is currently running. Finished with out errors.

SeanGallardy commented on the binary numbers. It took me a long time to see what he meant, but I finally entered the two numbers in the Windows Calculator in Programmer mode, and here is what it shows: the red shows the differences

Update: We did another backup and restore from the old server to the new server. This time we got this message:

Could not redo log record (2456609:4261461:64), for transaction ID (28:972770238), on page (48:211577379), allocation unit 72058359886184448, database 'DataWarehouse' (database ID 7). 
Page: LSN = (2456609:3279166:236), allocation unit = 72058351460417536, type = 20. Log: OpCode = 2, context 5, PrevPageLSN: (2456609:4250688:2). 
Restore from a backup of the database, or repair the database.

I have tried to Google this message, but the only advise I can find is to restore the database (which is what I'm trying to).

The real question is; what should I do next?

Improve this question
3
  • 3
    There is but a two byte difference between the two values in the most significant bytes. This could be an issue with controller cache, buffered IO issue on the file copy, etc. I'd double check hardware and drivers as well. Additionally you don't need a whole checkdb done, just target that file, table, and allocation for which there are checkdb commands.
    – Sean Gallardy
    Commented Feb 15, 2017 at 16:53
  • @SeanGallardy; I've tried to find this two byte difference with FreeHexEditor, but failed to see. Would you mind explaining it a bit more?
    – Henrik Staun Poulsen
    Commented Feb 16, 2017 at 8:05
  • 1
    1) Don't hex edit unless you want to restore. 2) your PAGE output shows that the on-disk structure is fine, it's most likely a memory issue, driver, firmware, etc. 3) In the initial error the actual value was: 29097436 but the expected value was: 8125916. If you convert this to little endian binary the expected was: 0000 0000 0111 1011 1111 1101 1101 1100 but the actual was: 0000 0001 1011 1011 1111 1101 1101 1100. You can see that this is an issue in the first two most significant bytes. Since, however it doesn't show up now, I'd check hardware, drivers, firmware, etc.
    – Sean Gallardy
    Commented Feb 16, 2017 at 15:50

1 Answer 1

Reset to default
1
+50

You do not have a backup until you did a restore.

The real question is; what should I do next?

This may not sound like the 'answer' to a DBA.SE question but, frankly, your only reasonable options here are to open a support case with Microsoft and ask for assistance, or hire a reputable professional (consultant or company) that has a proven record of fixing SQL Server corruption issues. If you can't afford these options, it means the data is not worth it.

Improve this answer
1
  • hi Remus; it helped. Microsoft suggested a RESTORE with CONTINUE_AFTER_ERROR. That gave us a page number, which gave us a table name. Which I dropped. They also suggested that we clear the log all the way down, and then back up to the 2TB. After doing both, the backup and restore worked without problems. Thank you all for all your help.
    – Henrik Staun Poulsen
    Commented Mar 13, 2017 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.