WRITEPAGE not picked up by CHECKDB

  • Hello !
    My goal was to create a simple corrupted database so that I could test that the error handling / reporting of my new maintenance plan.
    So I though I would create a small table, do a WRITEPAGE to change some data and voilà!
    Here is the code I used:

    USE master

    IF EXISTS(SELECT 1 FROM sys.databases WHERE name = 'Test_Corruption')
    BEGIN
        ALTER DATABASE Test_Corruption SET SINGLE_USER WITH ROLLBACK IMMEDIATE
        DROP DATABASE Test_Corruption
    END

    CREATE DATABASE Test_Corruption;
    GO
    ALTER DATABASE Test_Corruption MODIFY FILE (NAME = N'Test_Corruption', SIZE = 65536KB , FILEGROWTH = 65536KB)
    ALTER DATABASE Test_Corruption MODIFY FILE (NAME = N'Test_Corruption_log', SIZE = 65536KB , FILEGROWTH = 65536KB)
    ALTER DATABASE Test_Corruption SET RECOVERY SIMPLE
    ALTER DATABASE Test_Corruption SET PAGE_VERIFY CHECKSUM

    USE Test_Corruption
    SET NOCOUNT ON

    CREATE TABLE dbo.CorruptedTable (PK INT IDENTITY, Text CHAR(100) DEFAULT REPLICATE('a', 100), CONSTRAINT PK_CorruptedTable PRIMARY KEY (PK))
    GO
    INSERT INTO dbo.CorruptedTable (Text) VALUES (DEFAULT)
    GO 1000

    DECLARE @pages TABLE (PageFID BIGINT, PagePID BIGINT, IAMFID BIGINT, IAMPID BIGINT, ObjectID BIGINT, IndexID BIGINT, PartitionNumber BIGINT, PartitionID BIGINT, IAM_CHAIN_TYPE VARCHAR(100), PageType BIGINT, IndexLevel BIGINT, NextPageFID BIGINT, NextPagePID BIGINT, PrevPageFID BIGINT, PrevPagePID BIGINT)

    INSERT INTO @pages (PageFID, PagePID, IAMFID, IAMPID, ObjectID, IndexID, PartitionNumber, PartitionID, IAM_CHAIN_TYPE, PageType, IndexLevel, NextPageFID, NextPagePID, PrevPageFID, PrevPagePID)
        EXEC ('DBCC IND (N''Test_Corruption'', N''CorruptedTable'', -1)');

    DECLARE @pageIDToCorrupt BIGINT
    SELECT TOP(1) @pageIDToCorrupt = PagePID FROM @pages WHERE PageType = 1 AND PrevPagePID <> 0
    PRINT 'Corrupting Page ' + CAST(@pageIDToCorrupt AS VARCHAR(20));

    ALTER DATABASE Test_Corruption SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DBCC WRITEPAGE (N'Test_Corruption', 1, @pageIDToCorrupt, 190, 8, 0x1234567812345678, 1); 
    ALTER DATABASE Test_Corruption SET MULTI_USER

    DBCC CHECKDB (N'Test_Corruption') WITH ALL_ERRORMSGS

    /*
    --Recovery Code
    ALTER DATABASE Test_Corruption SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DBCC WRITEPAGE (N'Test_Corruption', 1, 330, 190, 8, 0x6161616161616161, 1);
    ALTER DATABASE Test_Corruption SET MULTI_USER
    DBCC CHECKDB (N'Test_Corruption') WITH ALL_ERRORMSGS
    */

    PAGE_VERIFY is obviously set to CHECKSUM and my understanding was that when the last parameter of WRITEPAGE was set to 1, the checksum would not be updated and should then fail.
    But it doesn't, CHECKDB runs fine, backups run fine. I do see my corrupted data "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa4Vx4Vxaaaaaa" with a SELECT but SQL Server is not complaining.
    I can make it fail by changing the offset to 200 instead of 190 because I'll be changing data across 2 slots but that's not exactly what I wanted to test.
    I've clearly misunderstood something but I'm not sure what. Is the checksum not protecting the actual data in a page? Is WRITEPAGE or CHECKDB updating the checksum? Anything else?
    Thank you for helping me understand what's happening!
    JM

  • Paul Randall wrote a lot about this, it may help.

    https://www.sqlskills.com/blogs/paul/corruption-demo-databases-and-scripts/

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Hello Michael, 
    Thank you for your time!
    I have already looked at Paul's blog, my setup script was inspired by Paul's script . His script works though, I'm getting a "SQL Server detected a logical consistency-based I/O error".
    I'm not sure what the difference is, I will dig in further.

  • I found a way to make it work but I don't understand it.
    I get no CHECKDB error when I write 8 bytes, however when I write anything from 1 to 7 then I get an error as I expected (i.e. Paul's script updated only 1 byte).
    In short, this will not cause any CHECKDB error.
    DBCC WRITEPAGE (N'Test_Corruption', 1, @pageIDToCorrupt, 190, 8, 0x1234567812345678, 1);
    But this will:
    DBCC WRITEPAGE (N'Test_Corruption', 1, @pageIDToCorrupt, 190, 1, 0x12, 1); 
    I have absolutely no explanation for this, I'm clearly out of my depth 🙂 Maybe something related to how SQL Server calculate its checksum?
    Anyone more knowledgeable has an idea?
    Thanks!

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

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