0

To get it out of the way, in this scenario we are repairing a number database where no backups were taken from before the corruption, so restoring a backup is not an option. Not my database :)

Upon running DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS we get thousands of errors that look like this:

Msg 8928, Level 16, State 1, Line 7
Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039762944 (type LOB data): Page (1:24911) could not be processed.  See other errors for details.
        Repairing this error requires other errors to be corrected first.
Msg 8965, Level 16, State 1, Line 7
Table error: Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039762944 (type LOB data). The off-row data node at page (1:24911), slot 0, text ID 265289728 is referenced by page (1:24820), slot 0, but was not seen in the scan.
        Repairing this error requires other errors to be corrected first.
Msg 8928, Level 16, State 1, Line 7
Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039762944 (type LOB data): Page (1:24912) could not be processed.  See other errors for details.
        Repairing this error requires other errors to be corrected first.
Msg 8965, Level 16, State 1, Line 7
Table error: Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039762944 (type LOB data). The off-row data node at page (1:24912), slot 0, text ID 265289728 is referenced by page (1:24820), slot 0, but was not seen in the scan.
        Repairing this error requires other errors to be corrected first.
Msg 8928, Level 16, State 1, Line 7
Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039762944 (type LOB data): Page (1:24913) could not be processed.  See other errors for details.
        Repairing this error requires other errors to be corrected first.

Running it repeatedly does not reduce the errors, so it looks like DBCC cannot repair this.

My next thought was to try and identify and delete the problematic rows in the table. When I tried deleting a known-problematic row though, it also errored, so my current thought is to extract known-good rows into a new table with the same schema as the old, drop the old table and rename the new one to match the old one.

The problem with this is that SQL Server, instead of providing a catchable error, simply drops the connection whenever a problem row is encountered, so I can't seem to find a programmatic way to identify the 'good' rows.

Is there any way in T-SQL to force it to provide a nice catchable error so I can iterate through the table and pull out the good rows, or some 'advanced' mode of DBCC CHECKDB that might be able to repair it that isn't obvious anywhere on the web?

3
  • You could perhaps do row by row lookup from a client script, and catch the dropped connection and move to the next row. How good are your backups, dare I ask..... Aug 18, 2021 at 2:33
  • Can you select all the non-"max" columns for all rows successfully? Your errors all mention the blob columns.
    – SMor
    Aug 18, 2021 at 2:44
  • Yes I can select every column for the whole table except for the blobs. So thinking about a solution I could potentially create a separate table to store the documents along with a flag for status, and have a two part script that updates a status to in progress, attempts to bring the record across and on restart removes any rows that were 'in progress' so as to not try the row again that dropped the connection...I will add an answer if it's successful :)
    – Thomas
    Aug 18, 2021 at 4:44

1 Answer 1

1

So I was able to get a fix working to extract out the working documents and then drop the problem table thanks to the help provided in the comments.

The below SQL on its own would need to be constantly re-executed manually, but I found by putting it inside OPENQUERY to a SQL Server linked to itself inside of a Try-Catch block I was able to get it to repeat the SQL until it was done.

Once complete just rename the Documents_rep table back to the original name and drop the table used as a quick and dirty fix to track whether the execution is complete.

I also ran across the problem of OpenQuery issuing an implicit transaction and rollback which I solved through sticking COMMIT; statements all through the script.

USE [Database];
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DocumentFixDone') BEGIN -- we dont want to start fixing a table that was already fixed
        IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DOCUMENTS_rep') BEGIN 
            --create a table to hold the repaired documents
            CREATE TABLE [dbo].[DOCUMENTS_rep](
                [RECORDID] [int] IDENTITY(1,1) NOT NULL,
                [CONTENT] [image] NULL,
                Status INT NOT NULL DEFAULT 0
                -- 0 = not tried
                -- 1 = In progress
                -- 2 = Successful
                -- 3 = Failed
            );
    
            --index the status field to make our searches faster
            CREATE INDEX    Documents_Status
            ON              Documents_rep
                            (Status);

            SET IDENTITY_INSERT Documents_rep on;
            --populate this with everything but the content from the broken table, default the status to 0
            INSERT INTO Documents_rep
                        ([RECORDID])
            SELECT  [RECORDID]
            FROM    DOCUMENTS

    
            SET IDENTITY_INSERT Documents_rep off;
        END

        --grab the in progress document from the last round and set it to failed
        UPDATE DOCUMENTS_rep 
        SET     Status = 3
        WHERE   Status = 1

        COMMIT; --need a commit because openquery starts a transaction


        DECLARE @currentRecordID int;

        --check if we have remaining rows and begin looping through and bringing them into the rep table
        WHILE EXISTS (SELECT 1 FROM Documents_rep WHERE Status IN (0)) BEGIN
            --get the lowest doc DB
            SET @currentRecordID = (SELECT min(REcordID) FROM Documents_rep WHERE Status = 0);

            --update it to in progress so if the script dies halfway through on restart it gets marked as invalid
            UPDATE  Documents_rep 
            SET     Status = 1 
            WHERE   RECORDID = @currentRecordID

            COMMIT; --need a commit because openquery starts a transaction

            --update the status to 2 and the content to be the equiv content from the docs table
            UPDATE  Documents_rep
            SET     Content = (SELECT d2.Content FROM Documents d2 WHERE d2.RECORDID = @currentRecordID), Status = 2
            WHERE   RecordID = @currentRecordID

            COMMIT; --need a commit because openquery starts a transaction

        END

        --track that weve completed the fix for documents
        CREATE TABLE DocumentFixDone (Recordid int);

        --once we are done, drop the old documents table, rename the existing one and move it across
        DROP TABLE Documents;
        DROP INDEX Documents_status ON DOCUMENTS_REP;
        ALTER TABLE Documents_rep 
        DROP COLUMN Status;
        ;

        COMMIT; --need a commit because openquery starts a transaction
    END
    ELSE
    BEGIN
        SELECT 'FixAlreadyComplete' AS FixStatus
    END

    
1
  • 1
    This is the approach I was thinking when asking that question. I'll add you need a proper disaster recovery process as well. One that is documented, known by more than one person, tested, and periodically validated. Part of that process is to test for corruption to know that the database is in a good state. And consider that corruption often indicates a problem with your disk. A much larger concern is the use of the IMAGE datatype - deprecated for almost 20 years.
    – SMor
    Aug 18, 2021 at 11:51

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.