Here is a handy reference for converting Page ID offset address to corresponding Page Number in SQL Server 2000:
- <offset>: This is the physical byte offset from the start of the file. Dividing this number by 8192 will give you the logical page number that is affected by the error.
Let’s try it practically based on one of the error message I have:
Error: 823, Severity: 24, State: 2
I/O error (bad page ID) detected during read at offset 0x00000000190000 in file ‘C:\Temp\Data\AW_Data.NDF’
Converting 8192 decimal value to Hex Value gives 2000
So the Page ID for this offset address is <Page Offset Address> / 2000 = 0x00000000190000 divide by 2000 = C8 in Hex
Converting C8 Hex to Decimal gives 200
So Page number of the offset address 0x00000000190000 is 200
Alternatively you can get the page offset of a page ID by multiplying Page ID with 8192 (both in decimal). So in our case 200 (Page Number) * 8192 (Page size) = 1638400 (Decimal)
Converting 1638400 to hex gives 190000 which is the Page offset address in hex.
Now you can use DBCC PAGE to dump out the contents of this Page and find details about the page like Object it belongs to, Page ID stored in the page header, Data stored in the page etc. You can refer to Storage Engine Team blog on usage of DBCC PAGE command.
DBCC PAGE in my case showed me that Page ID is 32 instead of 200 (Infact I corrupted it so I expect it to be wrong 🙂 :
In SQL Server 2005 and above you don’t need to do this calculation because the error message have the Page ID value for the offset:
Msg 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:200; actual 1:32). It occurred during a read of page (1:200) in database ID 27 at offset 0x00000000190000 in file ‘C:\Temp\Data\AW_Data.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.