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.
