主页 / dba / 问题 / 298178
Accepted
Thomas
Asked: 2021-08-18 15:12:54 +0800 CST

DBCC CheckDB REPAIR_ALLOW_DATA_LOSS 无法修复数据库时的策略

  • 772

为了解决这个问题，在这种情况下，我们正在修复一个数字数据库，其中在损坏之前没有进行任何备份，因此恢复备份不是一种选择。不是我的数据库:)

在使用 REPAIR_ALLOW_DATA_LOSS 运行 DBCC CHECKDB 时，我们会收到数千个如下所示的错误：

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.

反复运行它并不能减少错误，所以看起来 DBCC 无法修复它。

我的下一个想法是尝试识别并删除表中有问题的行。但是，当我尝试删除已知有问题的行时，它也出错了，所以我目前的想法是将已知良好的行提取到与旧表具有相同架构的新表中，删除旧表并重命名新表以匹配旧的。

这样做的问题是，SQL Server 没有提供可捕获的错误，而是在遇到问题行时简单地断开连接，因此我似乎无法找到一种编程方式来识别“好”行。

T-SQL 中是否有任何方法可以强制它提供一个很好的可捕获错误，以便我可以遍历表并提取好的行，或者 DBCC CHECKDB 的一些“高级”模式可能能够修复它在网络上的任何地方都不明显？

sql-server dbcc-checkdb

1 个回答

  1. Best Answer

    因此，由于评论中提供的帮助，我能够修复工作以提取工作文档，然后删除问题表。

    下面的 SQL 本身需要不断地手动重新执行，但我发现通过将它放在 OPENQUERY 到一个连接到自身的 SQL Server 的 Try-Catch 块内，我能够让它重复 SQL 直到它已完成。

    完成后，只需将 Documents_rep 表重命名为原始名称，然后删除用作快速和肮脏修复的表，以跟踪执行是否完成。

    我还遇到了 OpenQuery 发出隐式事务和回滚的问题，我通过坚持 COMMIT 解决了这个问题；语句贯穿整个脚本。

    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

相关问题