Nov 232011
 

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

Share/Bookmark

  20 Responses to “DBCC WritePage”

  1. Loverly Article John. Dangerous stuff but great for demos of Internals

  2. Great article, John! And scary too.

  3. Hi John

    I think that the segment in gray correspond to the null bitmap and the variable-lengh column offset array. First two bytes represent the number of columns in the record (02). Next two bytes are the number of variable lengh columns (00). The last two bytes (00) represent the offset to the start of the first lengh-variable column.

    • Thanks Ruben, My initial guess was same as yours but I knew that the bits right after fixed length columns are supposed to be NULL bitmap. There is no nullable columns in the table so there is no NULL bitmap. However, I don’t have variable length columns here neither, why SQL Server needs to record such information and let sapce to be wasted. interesting though.

  4. You really should remove this post – where did you learn about the undocumented (and completely NDA) final option to the command? AFAIK it’s never been discussed outside of Microsoft…

  5. Sigh – I see someone documented my undocumented option in the help. I guess this isn’t NDA – but it’s just irresponsible blogging about this (which is why I haven’t done so ever). You really should remove this – it’s going to cause people more trouble than help.

    • Paul, I’m responsible for the accuracy of my blog posts and findings. I could find this by using official commands about 2 hours. Others can do this as well. Microsoft should really close this command or at least make it not so easy to reach for supporting purpose. I do respect for you opinion. I put password on this post and share it within MCM community.

  6. John, excellent post and whilst I agree that this is a scary post, I don’t believe it should be removed since I dont think dbcc writepage is a huge secret – though it’s parameters are probably less well known. Rather than take down, I’d argue for a bigger warning at the top and bottom of the post.

    Thanks again.

  7. Just came across this – first time I’ve seen the last option.
    Awesome blog post, but I’d have to agree with Paul that it is pretty dicey.

    If you can’t run a hex editor this might be a viable alternative, but boy does it invite people to corrupt their databases!

    Hey, this an MCM’s blog after all, if you can’t take the heat, don’t play with matches…

  8. Hi John,

    very interesting article but i think a very important fact was omitted about this command and that is that the effect of this dbcc command isn’t logged in the transaction log and therefore can’t be executed in the context of an transaction an by that i’m think it is a only way road. I wonder if the request from Paul wasn’t connected with this situation.

    Also i think the last sentence “ALL YOUR OPERATIONS ARE LOGGED IN SQL Server LOG.” should be changed because when we read it we can misinterpret and think that you are talking about the transaction log (some people use sql server log and transaction log interchangeably) which is exactly the opposite of the truth.

    Anyway.. excelent article

  9. Hello, why DBCC writepage not directly modify SQL2005 and SQL2008 and SQL2008r2 contents?
    If DBCC writepage (‘ full ‘, 1, 15238, 8100, 1, 0 x00, 1) can modify content, but do not calculate page checksum;
    DBCC writepage (‘ full ‘, 1, 15238, 8100, 1, 0 x00, 0) that can’t modify data.

    • The last parameter 1 means write the data bypass the buffer pool. The checksum value is not calculated in this case.
      When the data in the buffer pool is not valid, you can’t modify the buffer pool.

      • Hello, why DBCC writepage not directly modify SQL2005 and SQL2008 and SQL2008r2 contents?
        DBCC writepage (‘ dbname ‘, 1, pageid, 8100, 1, 0 x00) that can’t modify SQL2005 data. In SQL2000 version can, senior version can’t, I think can fix content can recount checksum, could you tell me what should I do, can modify the content and can generate checksum again?

        • Yes, one way you get the checksum is that select records from the table that has corrupted page. You will see error Msg 824…incorrect checksum(expected:…actual…) the actual is the correct checksum value. Use DBCC write page with direct option on to write the checksum.

  10. Hi, DBCC writepage work in SQL2005 and above version, not calculation check? Why?

  11. Hi, DBCC writepage work in SQL2005 and above version, not calculation checksum? Why?

Add Comment Register



 Leave a Reply

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="">

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers: