Sql-server – How to Resolve Corruption Detected by BACKUP WITH CHECKSUM, But NOT DBCC CHECKDB

checksumdbcc-checkdbsql-server-2008-r2

Recently, I was asked to refresh a couple of databases on a test server from production. Always wanting to follow best practices, I performed the backup using the WITH CHECKSUM option, and eventually got the following error:

Msg 3043, Level 16, State 1, Line 32
BACKUP 'DBName' detected an error on page (1:428321) in file 'F:\path\DBName.mdf'.
Msg 3013, Level 16, State 1, Line 32
BACKUP DATABASE is terminating abnormally.

Surprised at the error, I ran DBCC CHECKDB to see what's going on, but unfortunately (or perhaps fortunately), it comes back clean.

All of the information I have found so far encourages the use of DBCC CHECKDB's findings as a guide on where to go next, so I'm not sure how to fix this?

Some quick info:

SQL Server is 2008 R2.
I found confirmation of the error in the msdb.dbo.suspect_pages table. It contains event types 1 and 3.
The DBCC command I used was DBCC CHECKDB (DBName) WITH NO_INFOMSGS, ALL_ERRORMSGS;
I am not the official DBA, but since I have some experience, I was permitted to perform the backup and restore.
I did read the similar post What types of corruption can DBCC CheckDB miss?. However, while I believe I understand why (the point of that post), I don't know how to resolve it.
I was able to successfully perform the backup by omitting the WITH CHECKSUM option.

Thanks in advance for any guidance!

UPDATE

The comment provided by "usr" below got me on the right track. Just what exactly does page (1:428321) contain? I did some more research and found the DBCC PAGE command. However, I noticed something odd. When I executed the following…

DBCC TRACEON (3604);
DBCC PAGE ('DBName', 1, 428321, 3);

I got the following back (note the page number on the first line)…

PAGE: (1:2021305)

BUFFER:

BUF @0x0000000CFEFCC580

bpage = 0x0000000CFE764000           bhash = 0x0000000000000000           bpageno = (1:428321)
bdbid = 18                           breferences = 0                      bcputicks = 0
bsampleCount = 0                     bUse1 = 20204                        bstat = 0xc00809
blog = 0xbcca2159                    bnext = 0x0000000000000000           

PAGE HEADER:

Page @0x0000000CFE764000

m_pageId = (1:2021305)               m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 5313   m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594386120704                                 Metadata: PartitionId = 0
Metadata: IndexId = -1               Metadata: ObjectId = 0               m_prevPage = (1:2021304)
m_nextPage = (1:2021306)             pminlen = 194                        m_slotCnt = 10
m_freeCnt = 228                      m_freeData = 7944                    m_reservedCnt = 0
m_lsn = (121329:238172:13)           m_xactReserved = 0                   m_xdesId = (0:0)
m_ghostRecCnt = 0                    m_tornBits = 297819981               

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           
PFS (1:420576) = 0x40 ALLOCATED   0_PCT_FULL                              DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Page 2021305??? Didn't I enter 428321? If I simply run the command with the page before it (428320), I get that page. So I guess I'm getting closer, but still don't know what that page contains.

Best Answer

I'm not sure how to fix this

In the comments we investigated but never found out conclusively whether the page is allocated or not.

If it's allocated this is definitely a corruption that CHECKDB missed. A bug in CHECKDB. Please report that to Microsoft.

If it's not allocated here's how I would fix it: Fill the file with dummy data e.g. CONVERT(binary(8000), 0x). Now the page should have been consumed by the dummy data and overwritten with something clean.

Actually, you can use this idea to test whether SQL Server thinks the page is allocated. If this works it's definitely not allocated.

Alternatively, you could use the write page feature to zero fill the page but that leaves an audit trace in the database boot page. Microsoft support will not like finding this, I guess, when they help you in the future with something. They might think your page write caused damage. Also, it steals all the fun from this investigation by terminating it without resolution :)

Related Solutions

Sql-server – DBCC CHECKDB unfixable corruption: Indexed view contains rows that were not produced by the view definition

The query processor can produce an invalid execution plan for the (correct) query generated by DBCC to check that the view index produces the same rows as the underlying view query.

The plan produced by the query processor incorrectly handles NULLs for the ImageObjectID column. It incorrectly reasons that the view query rejects NULLs for this column, when it does not. Thinking that NULLs are excluded, it is able to match the filtered nonclustered index on the Users table that filters on ImageObjectID IS NOT NULL.

By producing a plan that uses this filtered index, it ensures that rows with NULL in ImageObjectID are not encountered. These rows are returned (correctly) from the view index, so it appears there is a corruption when there is not.

The view definition is:

SELECT
    dbo.Universities.ID AS Universities_ID, 
    dbo.Users.ImageObjectID AS Users_ImageObjectID
FROM dbo.Universities
JOIN dbo.Users
    ON dbo.Universities.AdminUserID = dbo.Users.ID

The ON clause equality comparison between AdminUserID and ID rejects NULLs in those columns, but not from the ImageObjectID column.

Part of the DBCC generated query is:

SELECT [Universities_ID], [Users_ImageObjectID], 0 as 'SOURCE'
FROM [dbo].[mv_Universities_Users_ID] tOuter WITH (NOEXPAND) 
WHERE NOT EXISTS
( 
    SELECT 1 
    FROM   [dbo].[mv_Universities_Users_ID] tInner
    WHERE 
    (
        (
            (
                [tInner].[Universities_ID] = [tOuter].[Universities_ID]
            ) 
            OR 
            (
                [tInner].[Universities_ID] IS NULL
                AND [tOuter].[Universities_ID] IS NULL
            )
        )
        AND
        (
            (
                [tInner].[Users_ImageObjectID] = [tOuter].[Users_ImageObjectID]
            ) 
            OR 
            (
                [tInner].[Users_ImageObjectID] IS NULL 
                AND [tOuter].[Users_ImageObjectID] IS NULL
            )
        )
    )
)
OPTION (EXPAND VIEWS);

This is generic code that compares values in a NULL-aware fashion. It is certainly verbose, but the logic is fine.

The bug in the query processor's reasoning means that a query plan that incorrectly uses the filtered index may be produced, as in the example plan fragment below:

Erroneous plan

The DBCC query takes a different code path through the query processor from user queries. This code path contains the bug. When a plan using the filtered index is generated, it cannot be used with the USE PLAN hint to force that plan shape with the same query text submitted from a user database connection.

The main optimizer code path (for user queries) does not contain this bug, so it is specific to internal queries like those generated by DBCC.

Sql-server – CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object

My first question would be do you have latest valid backup or not. Since database size is just 6G and also because minimum repair suggested by checkdb is repair_allow_data_loss , which can cause data loss and could also remove business constraints from database, it would be good to restore database from valid backup.

Before restoring the backup you need to check validity of the backup. You can do it using below

restore verifyonly from disk='Backup location'

If it comes out clean you can restore the backup to get back the database. The data loss also depends upon the recovery model of database if it is simple you can only restore full and differential backup if it is bulk logged and Full you can restore full ,differential and log backups to get minimum data loss.

Are you able to take backup of current database with continue after error. Please see if below command works, I guess it would

backup database db_name to disk='Location' with continue_after_error

This would generate corrupt backup of database now restore this backup file ON DIFFERENT SERVER using continue after clause normal restore wont be possible see TSQL Restore for restore command.

After above is done run checkdb within transaction like below on above restored database.

Begin transaction
dbcc checkdb('db_name', repair_allow_data_loss)
--commit

After this you can have look into database how much data checkdb has deleted to recover database. Please note checkdb also removes constraints so I would not ask you to run it. Please read Books Online Document before proceeding

At last please see SQL Server errorlog and event viewer to see why database got corrupted

UPDATE

Best Answer

Related Solutions

Sql-server – DBCC CHECKDB unfixable corruption: Indexed view contains rows that were not produced by the view definition

Sql-server – CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object

Related Question