If you missed any of the earlier posts in my DR series, you can check them out here:
- 31 Days of disaster Recovery
- Does DBCC Automatically Use Existing Snapshot?
- Protection From Restoring a Backup of a Contained Database
- Determining Files to Restore Database
- Back That Thang Up
- Dealing With Corruption in a Nonclustered Index
- Dealing With Corruption in Allocation Pages
- Writing SLAs for Disaster Recover
- Resolutions for All DBAs
- Use All the Checksums
- Monitoring for Corruption Errors
- Converting LSN Formats
- Extreme Disaster Recovery Training
- Standard Backup Scripts
- Fixing a Corrupt Tempdb
- Running DBCC CheckTable in Parallel Jobs
- Disaster Recovery Gems From Around The Net
Checksum Page Verification
I’m going to start out by creating a new database named TestPageVerify, set page verification to NONE, and add a table with some data in it.
-- Create database for testing Create Database TestPageVerify; Go -- Set page verification to none Alter Database TestPageVerify Set Page_Verify None; Go -- Switch to the database Use TestPageVerify; Go -- Create a table with some data in it Select * Into dbo.AllDBs From sys.databases; Go -- Add a primary key Alter Table dbo.AllDBs Add Constraint PK_AllDBs_DBID primary key (database_id); Go
Next I will choose a column at random and use the undocumented function sys.fn_PhysLocFormatter(%%physloc%%) to return the file ID and page ID on which the record is located. %%physloc%% is a binary representation of where the row is located and fn_PhysLocFormatter breaks that down and returns the data as file ID, page ID, and slot ID formatted as (File:Page:Slot). After getting the results, I made a note of the location and the key value of the record that I’m going to messing with.
-- Find a random data page in the table -- (<File>:<Page>:<Row>): (1:277:3) -- database_id: 4 Select Top(1) sys.fn_PhysLocFormatter(%%physloc%%), database_id From dbo.AllDBs Order By NewID(); Go
If I use DBCC PAGE to dump the page in its current state, we’ll see that it is not protected. The key data flags to note in the header output are m_flagBits and m_tornBits. You’ll notice in this case that the flag bits are set to 0x0 meaning that checksum is not enabled and written to the page and torn bits are 0 as no value has been written yet.
-- Dump the page (any dump style) DBCC TraceOn(3604); DBCC Page(TestPageVerify, 1, 277, 1) -- From the header output: -- m_flagBits = 0x0 -- m_tornBits = 0 Go
Now, I’ll enable checksum page verification and recheck the flag bits and torn bits of the page. If you follow it up by running CHECKPOINT manually and rechecking the page, you’ll see that the values don’t change.
Alter Database TestPageVerify Set Page_Verify CheckSum; Go -- Dump the page again -- Still no change DBCC Page(TestPageVerify, 1, 277, 1) -- From the header output: -- m_flagBits = 0x0 -- m_tornBits = 0 Go
The next step is to update our sample record so that the page is dirtied in memory. At this point, dumping the page shows that the checksum value still has not been written and the torn bits is still 0. Running a CHECKPOINT will write the page to disk and cause these values to be updated. The second dump of the page shows that flag bits has been set to 0x200 (checksum page verification is enabled and populated) and torn bits is set to a large integer value.
Begin Tran Update dbo.AllDBs Set name = name + '_Test' Where database_id = 4; Commit Go -- Dump the page again -- Still no change DBCC Page(TestPageVerify, 1, 277, 1) -- From the header output: -- m_flagBits = 0x0 -- m_tornBits = 0 Go CHECKPOINT; Go -- Dump the page again -- Still no change DBCC Page(TestPageVerify, 1, 277, 1) -- From the header output: -- m_flagBits = 0x200 -- m_tornBits = 655784296 Go
The next thing I want to test is to add a large, fixed-length column to the table so I can update the record and force it to be moved to a new page. I’ll then use the fn_PhysLocFormatter function again to identify the new page where the record is located.
-- Add a 7600 fixed length column so we can force a page split Alter Table dbo.AllDBs Add TestVal nchar(3800) null; Go Begin Tran Update dbo.AllDBs Set TestVal = N'Test' Where database_id = 4; Commit Go -- Find the same page in the table -- (<File>:<Page>:<Slot>): (1:282:0) Select Top(1) sys.fn_PhysLocFormatter(%%physloc%%), database_id From dbo.AllDBs Where database_id = 4; Go
Dumping the page header will show that the checksum value is not yet set as both flag bits and torn bits show 0. Running a manual CHECKPOINT and re-dumping the new page shows that the checksum value is not written to the page.
-- Dump the new page -- No checksum info DBCC Page(TestPageVerify, 1, 282, 1) -- From the header output: -- m_flagBits = 0x0 -- m_tornBits = 0 Go CHECKPOINT; Go -- Dump the page again -- Still no change DBCC Page(TestPageVerify, 1, 282, 1) -- From the header output: -- m_flagBits = 0x200 -- m_tornBits = 309696659 Go
Nothing left now, but to disable the trace flag we enabled and drop the test database.
-- Disable DBCC Trace DBCC TraceOff(3604); -- Cleanup database Use master; If DB_ID('TestPageVerify') Is Not Null Drop Database TestPageVerify; Go
Summary
Run through the demo above (also attached below) and you will see that page checksums are not written to the page until the page is written to disk. Simply setting page verification to checksum is not sufficient. My recommendation is to plan to rebuild all indexes and heaps at your next index maintenance window to ensure all data gets rewritten. Beginning with SQL Server 2008, you can rebuild a heap with the ALTER TABLE REBUILD; command.
Demo script: WhenChecksumsAreWritten.zip (1 KB)
Day 25 of 31 Days of Disaster Recovery: Improving Performance of Backups and Restores | SQLSoldier
[…] When are Checksums Written to a Page […]
Day 26 of 31 Days of Disaster Recovery: The Mysterious Case of the Long Backup | SQLSoldier
[…] When are Checksums Written to a Page […]