October 22, 2018 at 9:07 am
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
October 22, 2018 at 9:23 am
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/
October 22, 2018 at 9:35 am
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.
October 22, 2018 at 10:47 am
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