January 4, 2021 at 10:16 pm
Hi all,
Please see attached for the output of DBCC CHECKTABLE on one of our tables. The affected records are almost certainly attachments we are storing as VARBINARY in the table.
I'd like to know what my options are. Restoring to a previous backup is unfortunately not possible.
I am okay with repairing with data loss, provided that there is some way of me being able to identify which records were affected; I assume that the repair will result in the attachments being lost for the affected records, but I'd need to be able to identify which they were in that case.
Is there any way of being able to identify them without running any repair? If so, I don't mind simply clearing out the attachment data for those records first, if that would help.
Thanks for any insights!
January 4, 2021 at 10:51 pm
Based on the errors - I am not sure you have the option to repair. As for identifying the data - you can try inspecting every page using DPCC PAGE and see if that identifies the data enough for you.
BTW - why isn't restoring from a previous backup an option?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 4, 2021 at 11:09 pm
Will try the page check option and see if that gets me any more info.
Regarding restoring from a previous backup - to clarify, I do have a working backup from about a month and a half ago. If I were to restore from that backup, though, would I have any way of being able to identify what rows were problematic? I'd still need to compare the current database with the restore, so how would I know? Can I check the equality of the VARBINARY data to see what the differences are?
January 4, 2021 at 11:33 pm
Jeffrey - here's the output from one of the DBCC PAGE queries. Unfortunately I have no idea how to read this. Do you happen to see something that would identify the affected record?
ParentObject Object Field VALUE
BUFFER: BUF @0x0000000318104E00 bpage 0x0000000277636000
BUFFER: BUF @0x0000000318104E00 bhash 0x0000000000000000
BUFFER: BUF @0x0000000318104E00 bpageno (1:101273)
BUFFER: BUF @0x0000000318104E00 bdbid 6
BUFFER: BUF @0x0000000318104E00 breferences 0
BUFFER: BUF @0x0000000318104E00 bcputicks 0
BUFFER: BUF @0x0000000318104E00 bsampleCount 0
BUFFER: BUF @0x0000000318104E00 bUse1 62079
BUFFER: BUF @0x0000000318104E00 bstat 0x809
BUFFER: BUF @0x0000000318104E00 blog 0x15a
BUFFER: BUF @0x0000000318104E00 bnext 0x0000000000000000
PAGE HEADER: Page @0x0000000277636000 m_pageId (1:101273)
PAGE HEADER: Page @0x0000000277636000 m_headerVersion 1
PAGE HEADER: Page @0x0000000277636000 m_type 3
PAGE HEADER: Page @0x0000000277636000 m_typeFlagBits 0x0
PAGE HEADER: Page @0x0000000277636000 m_level 0
PAGE HEADER: Page @0x0000000277636000 m_flagBits 0xa200
PAGE HEADER: Page @0x0000000277636000 m_objId (AllocUnitId.idObj) 276
PAGE HEADER: Page @0x0000000277636000 m_indexId (AllocUnitId.idInd) 256
PAGE HEADER: Page @0x0000000277636000 Metadata: AllocUnitId 72057594056015872
PAGE HEADER: Page @0x0000000277636000 Metadata: PartitionId 72057595066318848
PAGE HEADER: Page @0x0000000277636000 Metadata: IndexId 1
PAGE HEADER: Page @0x0000000277636000 Metadata: ObjectId 1479676319
PAGE HEADER: Page @0x0000000277636000 m_prevPage (0:0)
PAGE HEADER: Page @0x0000000277636000 m_nextPage (0:0)
PAGE HEADER: Page @0x0000000277636000 pminlen 0
PAGE HEADER: Page @0x0000000277636000 m_slotCnt 1
PAGE HEADER: Page @0x0000000277636000 m_freeCnt 26
PAGE HEADER: Page @0x0000000277636000 m_freeData 8164
PAGE HEADER: Page @0x0000000277636000 m_reservedCnt 0
PAGE HEADER: Page @0x0000000277636000 m_lsn (538:1780:26)
PAGE HEADER: Page @0x0000000277636000 m_xactReserved 0
PAGE HEADER: Page @0x0000000277636000 m_xdesId (0:0)
PAGE HEADER: Page @0x0000000277636000 m_ghostRecCnt 0
PAGE HEADER: Page @0x0000000277636000 m_tornBits -810479296
PAGE HEADER: Page @0x0000000277636000 DB Frag ID 1
PAGE HEADER: Allocation Status GAM (1:2) ALLOCATED
PAGE HEADER: Allocation Status SGAM (1:3) NOT ALLOCATED
PAGE HEADER: Allocation Status PFS (1:97056) 0x44 ALLOCATED 100_PCT_FULL
PAGE HEADER: Allocation Status DIFF (1:6) NOT CHANGED
PAGE HEADER: Allocation Status ML (1:7) NOT MIN_LOGGED
January 5, 2021 at 1:01 pm
You may be able to overwrite or repair the data providing you have a backup, including TLog backups, that does not have this issue when restored and a DBCC CHECKDB run against it.
Look in the system tables and identify the table that ObjectId 1479676319 refers to. IndexId 1 indicates that this is a clustered index.
PAGE HEADER: Page @0x0000000277636000 Metadata: IndexId 1
PAGE HEADER: Page @0x0000000277636000 Metadata: ObjectId 1479676319
To get the details within a page regarding row data, have a look at this mssqltips article. The page in question is 101273 in file ID 1, your MDF file.
PAGE HEADER: Page @0x0000000277636000 m_pageId (1:101273)
To test your recovery process, restore a copy of the database that has the issue elsewhere, repair it and investigate any resulting issues.
Hope this helps.
January 5, 2021 at 3:12 pm
Hey Numpty,
The output I had pasted is the result of DBCC PAGE against that page. I'm not sure how to interpret that output though; is there something in there which would tell me what the record is?
January 5, 2021 at 3:22 pm
The page output doesn't appear to be the data page - you should review the article at https://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/
What you need is the page with the data associated with that header page to see if there is anything on that page that can help you identify the data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 5, 2021 at 3:25 pm
Will try the page check option and see if that gets me any more info.
Regarding restoring from a previous backup - to clarify, I do have a working backup from about a month and a half ago. If I were to restore from that backup, though, would I have any way of being able to identify what rows were problematic? I'd still need to compare the current database with the restore, so how would I know? Can I check the equality of the VARBINARY data to see what the differences are?
Is the database in simple or full recover model? If full - do you have transaction log backups from that backup to current?
You can restore that old copy of the database as a different database and compare the data in the table in the restored database with the table in the current database. But since it is so old I am not sure what you can do with that information - it all depends on how that data is managed in the table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 5, 2021 at 3:32 pm
Ah. Roger. Will take a look and report back. Thanks!
January 5, 2021 at 3:32 pm
kramaswamy wrote:Will try the page check option and see if that gets me any more info.
Regarding restoring from a previous backup - to clarify, I do have a working backup from about a month and a half ago. If I were to restore from that backup, though, would I have any way of being able to identify what rows were problematic? I'd still need to compare the current database with the restore, so how would I know? Can I check the equality of the VARBINARY data to see what the differences are?
Is the database in simple or full recover model? If full - do you have transaction log backups from that backup to current?
You can restore that old copy of the database as a different database and compare the data in the table in the restored database with the table in the current database. But since it is so old I am not sure what you can do with that information - it all depends on how that data is managed in the table.
Full, but unfortunately the transaction log backup chain from that previous backup isn't proper, so I can't make use of them :/
January 5, 2021 at 3:38 pm
That is unfortunate - I would recover from the full backup (to a new database) and apply all transaction log backups you do have available and then compare the data in the affected table with the current table.
I am still not sure if the repair option is available - you may need to run a full integrity check on the database to see if SQL can even repair the corruption with data loss.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 5, 2021 at 5:01 pm
So I did a bit more digging - running DBCC PAGE('mytable', 1, 101273, 2) WITH TABLERESULTS works, and produces the DATA output rows, but since it's a VARBINARY column, I can't really interpret them.
Running the same PAGE query with the flag set to either 1 or 3, though, produces an error. I'm not too sure how to interpret it, but essentially it says:
Access Violation occurred reading address 0000000012671B64
Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
Exception Address = 000007FEDF01CFDA Module(sqlmin+0000000002C7CFDA)
Running the same DBCC PAGE query against 101272 and 101274 works properly. If we assumed the pages were in the same order as the actual records (which, I know, is not necessarily a guarantee), is there any way I could use those two other pages to figure out what record is in between them?
January 5, 2021 at 5:10 pm
I don't think that is going to be possible - and I was fairly certain you would not be able to identify anything by the varbinary column. You might be able to take the varbinary data and try converting it to varchar - again, not sure that is going to get you anything or if it is even possible. You would have to copy it from the DBCC PAGE output.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 5, 2021 at 5:41 pm
Oh! I might have found something!!
I was reading through the original output, and I noticed that at the end there are several records of the following nature:
Msg 8929, Level 16, State 1, Line 1
Object ID 1479676319, index ID 1, partition ID 72057595066318848, alloc unit ID 72057595150598144 (type In-row data): Errors found in off-row data with ID 10027008 owned by data record identified by RID = (1:205891:7)
Am I correct in interpreting the first two numbers in (1:205891:7) as the file num and page num? IE, file #1, page #205891?
If so, that points at the primary data record, which gives me the rest of the record information, including the primary key value. If I'm correct, and that is indeed the identifying record, am I correct in assuming that if I simply update the record and set the VARBINARY data for that record to NULL, it might fix the problem? Or, perhaps, even simply deleting the record outright?
January 5, 2021 at 5:52 pm
This is correct and I have already pointed this out to you. You may also be able to identify the data row using the RID.
You should also be able to test a row by trying to select it. Or limiting the selection to a few columns ti pin down the exact issue.
You may be able to overwrite or repair the data providing you have a backup, including TLog backups, that does not have this issue when restored and a DBCC CHECKDB run against it.
Look in the system tables and identify the table that ObjectId 1479676319 refers to. IndexId 1 indicates that this is a clustered index.
PAGE HEADER: Page @0x0000000277636000 Metadata: IndexId 1
PAGE HEADER: Page @0x0000000277636000 Metadata: ObjectId 1479676319To get the details within a page regarding row data, have a look at this mssqltips article. The page in question is 101273 in file ID 1, your MDF file.
PAGE HEADER: Page @0x0000000277636000 m_pageId (1:101273)To test your recovery process, restore a copy of the database that has the issue elsewhere, repair it and investigate any resulting issues.
Hope this helps.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply