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?

  • 3
    Since you have the page number, you could see if you can read that page on production although I would certainly try very very hard to ensure you have a reliable backup that you can restore somewhere first. – Max Vernon Feb 15 '17 at 14:56
  • 1
    Which version of SQL Server is this? You could do a page restore if you are using at least 2008. – Tara Kizer Feb 15 '17 at 15:17
  • 1
    Does the production server have checksum page verification turned on? – Max Vernon Feb 15 '17 at 15:23
  • 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 says Remove Sara Chipps Feb 15 '17 at 16:53
  • 1
    @MaxVernon; we are trying to restore the backup onto a new server. It has taken 3 days to move the 25TB backup files to Copenhagen. Then RESTORE failed. Bummer. I'll try to get CheckAlloc and/or CheckFileGroup run tomorrow morning. Updated question. – Henrik Staun Poulsen Feb 15 '17 at 21:06
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.

  • Hi Remus; Case created with a status B, so now Microsoft have until 10AM tomorrow to react. Thank you for your help. – Henrik Staun Poulsen Mar 2 '17 at 20:45
  • 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 Mar 13 '17 at 14:16
  • 1
    Thanks for posting the recommendation so others can see and try – Remus Rusanu Mar 13 '17 at 14:49
1

Henrik,

Try to provide the output of DBCC CHECKDB. You should read this thread: DBCC CHECKDB consistency-based I/O error

I hope it will help you.

Good Luck!

  • as I wrote; A DBCC CheckDB will take us about a week, and even if we have a 4 TB transaction log, that would not be big enough. – Henrik Staun Poulsen Mar 2 '17 at 20:44
  • the DBCC CheckDB only took 3½ days on the new server, with MaxDOP=40. It "found 0 errors and repaired 0 errors." – Henrik Staun Poulsen Mar 28 '17 at 7:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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