SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

John Huang’s Blog

John Huang, Microsoft Certified Master in SQL Server, SQL Server MVP, is an independent SQL Server consultant in Vancouver BC, Canada. He started using SQL Server for his projects in year 1994. He has architected and implemented many SQL and BI applications serving different industrial areas. He loves talking about SQL Server and discussing SQL Server technologies with others. He blogs at http://www.sqlnotes.info.

Protect Yourself from Using DBCC WritePage

I blogged DBCC WritePage a year ago here http://www.sqlnotes.info/2011/11/23/dbcc-writepage/. It’s an extremely dangerous command especially with the last parameter “directORbufferpool” of this command turned on. I also showed you how to fix the page checksum here http://www.sqlnotes.info/2013/05/02/fix-page-checksum/. To protect yourself, please ensure you are operating on the database that you want, not system databases. Taking a full backup before playing this command is important. Never run it in your production. Beside that, there is a simple way to rollback changes made by DBCC WritePage…

When use DBCC Page to read the data. Data will come to the buffer pool first. When the page is not valid, wrong page checksum, conceptually, data will bypass the buffer pool and directly returned to the user.
When use DBCC WritePage to write the data. Data changes will be done in the buffer pool. Buffer pool will harden the changes to disk. What will happen if the file is read only logically? Will DBCC WritePage returns error? Let’s take a look

use master
if db_id('TestDB') is not null
	drop database TestDB
create database TestDB 
create table TestDB.dbo.t1(Field1 char(10))
insert into TestDB.dbo.t1 values('AAAAAAAAAA')
select * from TestDB.dbo.t1
-- Field1
-- ----------
---set the database to read only
alter database TestDB set read_only 
delete TestDB.dbo.t1
--Msg 3906, Level 16, State 1, Line 1
--Failed to update database "TestDB" because the database is read-only.
dbcc traceon(3604)
dbcc ind(TestDB, t1, 1)

--PageFID PagePID     PageType 
--------- ----------- -------- 
--1       150         10       
--1       147         1        
dbcc page(TestDB, 1, 147,3)
dbcc writepage(TestDB, 1, 147,105, 3, 0x424344 )
select * from TestDB.dbo.t1 --  data get changed without any error
-- this change is done in the memory. 
-- Now let's change the database state
alter database TestDB set read_write
-- Check it again. all changes done by DBCC WritePage are "rolled back"
select * from TestDB.dbo.t1 

Turn your database into read-only mode can protect you from being hurt by DBCC WritePage
John Huang – SQL MCM & MVP, http://www.sqlnotes.info


Leave a comment on the original post [www.sqlnotes.info, opens in a new window]

Loading comments...