Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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.

DBCC WritePage

DBCC WritePage is a dangerous undocumented DBCC command. It’s useful in your sandbox to change data, crash objects, and do some fun things you may think of. But never ever use it in your productions. Using DBCC WritePage command is at your own risk. . I will give you demos on how to use dbcc writepage to perform simple insert, delete, and update on a table.

dbcc writepage ({'dbname' | dbid}, fileid, pageid, offset, length, data [, directORbufferpool])

{‘dbname’ | dbid}
Database name or database id
fileid
File id in sys.database_files
pageid
Page number in the file
offset
Starting position of the data you want to change
length
number of bytes to be written to the page
data
data to be written. It’s binary, for example 0×13432
directORbufferpool
2 possible values, 0 and 1. When it’s 0, which is also the default value, the data modification is done at buffer level, related data will be calculated automatically such as page hash. When it’s 1, the modification is written to disk directly, related values, such as page hash, will not be recalculated automatically. Unless you want to test your math level…

Prepare Data
First of all, let’s prepare some data for test.

use master
set nocount on
if DB_ID('test') is not null
begin
	alter database test set read_only with rollback immediate
	drop database test
end
go
create database test;
go
alter database test set recovery simple
alter database test set auto_create_statistics off
alter database test set auto_update_statistics  off
alter database test set allow_snapshot_isolation off
alter database test set read_committed_snapshot off
go
use test
create table test(id int not null, t char(20) not null)
insert into test values(1, REPLICATE('a', 20))
insert into test values(2, REPLICATE('b', 20))
insert into test values(3, REPLICATE('c', 20))
select * from test
/*
id          t
----------- --------------------
1           aaaaaaaaaaaaaaaaaaaa
2           bbbbbbbbbbbbbbbbbbbb
3           cccccccccccccccccccc
*/

We have 3 records in table test. Then let’s check where the data located

dbcc traceon(3604) with no_infomsgs
go
dbcc ind(test,'test', 1) with no_infomsgs -- first parameter is the database name, second is table name
/* 
PageFID PagePID     IAMFID IAMPID      ObjectID    --I removed rest of the columns returning from dbcc ind
------- ----------- ------ ----------- ----------- 
1       154         NULL   NULL        2105058535  -- this is IAM page
1       153         1      154         2105058535  -- this is database
*/

Then let’s check what’s on the page

dbcc page(test, 1, 153, 2) with no_infomsgs

It will return

PAGE: (1:153)

BUFFER:

BUF @0x0000000085FC0A00

bpage = 0x0000000085570000           bhash = 0x0000000000000000           bpageno = (1:153)
bdbid = 13                           breferences = 0                      bcputicks = 0
bsampleCount = 0                     bUse1 = 28677                        bstat = 0xc0010b
blog = 0x212121bb                    bnext = 0x0000000000000000           

PAGE HEADER:

Page @0x0000000085570000

m_pageId = (1:153)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 27     m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594039697408                                 
Metadata: PartitionId = 72057594038779904                                 Metadata: IndexId = 0
Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 28                         m_slotCnt = 3                        m_freeCnt = 7997
m_freeData = 189                     m_reservedCnt = 0                    m_lsn = (99:68:2)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                       

Allocation Status

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

DATA:

Memory Dump @0x000000001103C000

000000001103C000:   01010400 00800001 00000000 00001c00 †................ 
000000001103C010:   00000000 00000300 1b000000 3d1fbd00 †............=.½. 
000000001103C020:   99000000 01000000 63000000 44000000 †........c...D... 
000000001103C030:   02000000 00000000 00000000 00000000 †................ 
000000001103C040:   01000000 00000000 00000000 00000000 †................ 
000000001103C050:   00000000 00000000 00000000 00000000 †................ 
000000001103C060:   10001c00 01000000 61616161 61616161 †........aaaaaaaa 
000000001103C070:   61616161 61616161 61616161 02000010 †aaaaaaaaaaaa.... 
000000001103C080:   001c0002 00000062 62626262 62626262 †.......bbbbbbbbb 
000000001103C090:   62626262 62626262 62626202 00001000 †bbbbbbbbbbb..... 
000000001103C0A0:   1c000300 00006363 63636363 63636363 †......cccccccccc 
000000001103C0B0:   63636363 63636363 63630200 00000021 †cccccccccc.....! 
000000001103C0C0:   21212121 21212121 21212121 21212121 †!!!!!!!!!!!!!!!! 
000000001103C0D0:   21212121 21212121 21212121 21212121 †!!!!!!!!!!!!!!!! 
....
000000001103DFE0:   21212121 21212121 21212121 21212121 †!!!!!!!!!!!!!!!! 
000000001103DFF0:   21212121 21212121 21219e00 7f006000 †!!!!!!!!!!?...`. 

OFFSET TABLE:

Row - Offset                         
2 (0x2) - 158 (0x9e)                 
1 (0x1) - 127 (0x7f)                 
0 (0x0) - 96 (0x60)

Update Record
I put update first since it’s the simplest comparing other 2 examples.

dbcc traceon(2588) with no_infomsgs 
go
-- change the first record
dbcc writepage(test, 1, 153, 105, 3, 0x626364)
go
select * from test
/*
id          t
----------- --------------------
1           abcdaaaaaaaaaaaaaaaa
2           bbbbbbbbbbbbbbbbbbbb
3           cccccccccccccccccccc
*/

Look at the result, the first record is changed to abcdaaaaaaaaaaaaaaaa from aaaaaaaaaaaaaaaaaaaa. Run dbcc checktable('test'). No error return. That’s great.

Insert
First, let’s check the slot array from the dump of dbcc page, line 53. The first record is at 0×0060 and the second is at 0x007f. The size of the record is 31, 0x7f – 0×60 = 0x1f = 31. Copy the binary from that range

0x10001c00010000006161616161616161616161616161616161616161020000

  • Segment in blue:  Record header
  • Segment in maroon: The the first field. It’s 4 byte integer value. Lower address value contains lower byte in the integer. The value of it is 0×00000001.
  • Segment in red: The second field.
  • Segment in gray: I don’t know what that is. If you know, please tell me.

base on that, we can make a new record below. The location we should put the new record is saved in m_freeData in the header.

0x10001c00040000006162636465666768696a6b6c6d6e6f7071727374020000

Now let’s use dbcc page to put the record to the page atbyte 189 which is 0x00BD in hexadecimal.

 -- I assume you have turned on trace flag 2588
--write new record to the page, you will not get any errors after running this statement
dbcc writepage(test, 1, 153, 189, 31, 0x10001c00040000006162636465666768696a6b6c6d6e6f7071727374020000) 

--Add a new record into the slot array at the end of the page. Ignore the page validation error. 
dbcc writepage(test, 1, 153, 8184, 2, 0xbd00) -- change slot array

--Change number of count of record on the page from 3 to 4. Ignore the page validation error. 
dbcc writepage(test, 1, 153, 22, 2, 0x0400) -- change m_slotCnt

--Change the location where the free space starting from. Ignore the page validation error. 
--page has 8192 bytes. First 96 bytes is the header of the page
--we have 4 records, each of them is 31 bytes.
--So the free space will be
--8192-96-4*31-4*2 (this is the size of slot array) = 7964 = 0x1f1c
dbcc writepage(test, 1, 153, 28, 2, 0x1C1F) -- change m_freeCnt

--Change the location where the free space starting from. Ignore the page validation error. 
--This can be calculated by m_freeData =  length of the record = 189 + 31 = 220 = 0x00dc
dbcc writepage(test, 1, 153, 30, 2, 0xDC00) -- change m_freeData

dbcc checktable('test')
/*
DBCC results for 'test'.
There are 4 rows in 1 pages for object "test".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/

--good, we passed dbcc checktable test
select * from test
/*
id          t
----------- --------------------
1           abcdaaaaaaaaaaaaaaaa
2           bbbbbbbbbbbbbbbbbbbb
3           cccccccccccccccccccc
4           abcdefghijklmnopqrst
*/

Delete
Now let’s remove the record where id = 3. It’s way simple to remove a record from a heap than from a B-Tree. We only need to change the slot array to 0×0000 then update some values on the page header. You will see the page validation error for each of the step. You can ignore it. Finally, we will run dbcc checktable to verify the changes.

--change the slot array from offset 8186
dbcc writepage(test, 1, 153, 8186, 2, 0x0000) -- change slot array

--change number of records on the page in the page header
dbcc writepage(test, 1, 153, 22, 2, 0x0400) -- change m_slotCnt

--change free bytes on the page.
--before deletion, page has 7964 free bytes
--after deletion, 31 bytes freed from the page and 2 bytes freed from the slot array
--So the free count is 7964+31+2=7997=0x1f3d
dbcc writepage(test, 1, 153, 28, 2, 0x3B1F) -- change m_freeCnt

-- Then update other flags
dbcc writepage(test, 1, 153, 38, 2, 0x1f00) -- change m_reservedCnt
dbcc writepage(test, 1, 153, 50, 2, 0x1f00) -- change m_xactReserved
dbcc writepage(test, 1, 153, 4, 1, 0x08) -- change m_flagBits
go
dbcc checktable('test')
/*
DBCC results for 'test'.
There are 3 rows in 1 pages for object "test".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
--perfect, we passed dbcc checktable test
select * from test
/*
id          t
----------- --------------------
1           abcdaaaaaaaaaaaaaaaa
2           bbbbbbbbbbbbbbbbbbbb
4           abcdefghijklmnopqrst
*/

The record where id = 3 is gone! Cheers! It’s not easy to manipulate data on a page by dbcc writepage command especially when you don’t have official documentation. These 3 experiments took me about 2 hours. That’s just the modification on heap with fixed length required columns on one page. It will be way more complicated for other type of manipulationi since they may need you to deal with nulls, variable lengh columns, out of row data, IAM, PFS, GAM, SGAM,…to much information.
Finally, I would like remind you — !!DON’T RUN THIS COMMAND ON YOUR PRODUCTION SERVERS!! ALL YOUR OPERATIONS ARE LOGGED IN SQL Server ERROR LOG.