DBCC WritePage

 

DBCC WritePage and undocumented DBCC Command

DBCC WritePage is  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.

Syntax: 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 0x13432

• 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 . 

The first record is at 0x0060 and the second is at 0x007f. The size of the record is 31, 0x7f – 0x60 = 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 0x00000001.
• 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 0x0000 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 length columns, out of row data, IAM, PFS, GAM, SGAM,…to much
information.