Anyway to find the row based on the page number

Question

Anyway to find the row based on the page number

Ant-Green

SSC Guru

Points: 113445
More actions
August 31, 2017 at 2:37 am

#402450

Working with a new client and on one of the DB's I am looking at has a consistency error on one of the tables.
I have dropped and rebuilt all of the indexes on the table and run a DBCC UPDATEUSAGE as the page counts where off for that table, but the consistency error still remains.
The page is for a LOB and was just wondering if there's anything in the page which can be used to tie it back to the row it belongs to so we can remove the file from the SharePoint databases
There has been a ticket for this since the start of the year and all backups I can get my hands on have the issue.
DBCC CHECKDB output is
Table Error: Object ID 926626344, index ID 1, partition ID 72057594244497408, alloc unit ID 72057594055688192 (type LOB data). The off-row data npde at page (1:2910904), slot 0, tect ID 761462784 is not referenced.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 1

DBCC PAGE might give some more information... You'll need to pass in the database ID, the fileID & page ID, and a formatting option (0-3):

DBCC PAGE (DB_ID(), 1, 2910904, 0)

along with this post: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2006/06/10/how-to-use-dbcc-page/ should give you a starting point.

Good luck!

Thomas Rushton
blog: https://thelonedba.wordpress.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

In this case it's an orphaned text blob.

The off-row data node is not referenced

As such, the row that the text blob belongs to is gone somehow and hence there's nothing you can delete.

If this is the only error you have, a repair with allow_data_loss will remove just the orphaned text blob with no actual data loss (the data loss has already happened sometime in the past)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

Thanks Thomas, yes been trying to use DBCC PAGE to try and work my way back to the offending row with no luck.

Thanks also Gail, yes that is the only error now remaining after fixing the other errors DBCC CHECKDB gave. I will get backups and recovery procedures in places and then crack on with the repairs.