DBCC WRITEPAGE: an introduction

(New for 2020: we’ve published a range of SQL Server interview candidate screening assessments with our partner Kandio, so you can avoid hiring an ‘expert’ who ends up causing problems. Check them out here.)

(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.)

SQL Server’s undocumented commands, options, and trace flags are sometimes carefully-guarded secrets. I’ve been a major secret-keeper of lots of these, having written/re-written a bunch of the undocumented DBCC commands. I’ve been assiduously avoiding writing this blog post for 14 years but now my hand has been forced.

There’s one command in particular, DBCC WRITEPAGE, that has been effectively hidden since it was introduced pre-SQL Server 2000. However, more and more people are learning about it since some of the exam training books publicized trace flag 2588 that allows DBCC HELP (‘?’) to show all the undocumented DBCC commands, and their syntax (it used to be trace flag 2520, but I changed it for SQL Server 2005 to help obfuscate access to the undocumented DBCCs).

Some people are even starting to blog and present about the more obscure commands, including DBCC WRITEPAGE. I asked, in vain, for DBCC WRITEPAGE to not be publicized so I now feel compelled to blog about it so there is authoritative information available about it (I rewrote and extended its functionality for SQL Server 2005). I discussed this issue with the SQL product team and CSS last week, and they agree with me doing this. You won’t see a Microsoft post about this, but my great friend Bob Ward from CSS wanted me to quote him saying “Microsoft does not support the usage of DBCC WRITEPAGE and its use would invalidate the support for a customer database on which it is used.”

I present to you DBCC WRITEPAGE – the most dangerous command you can use in SQL Server.

*** DISCLAIMER: The information in this blog post should not be used on a production SQL Server system. Any problem, corruption, damage, or loss you cause by using the information presented here is entirely your own responsibility. Use at your own risk. Danger of death. ***

Well, not danger of death :-)

DBCC WRITEPAGE allows you alter any byte on any page in any database, as long as you have sysadmin privileges. It also allows you to completely circumvent the buffer pool, in other words you can force page checksum failures – this was the part I wrote from scratch, and I’m rather proud of its sneakiness.

The purposes of DBCC WRITEPAGE are:

  1. To allow automated testing of DBCC CHECKDB and repair by the SQL Server team.
  2. To engineer corruptions for demos and testing.
  3. To allow for last-ditch disaster recovery by manually editing a live, corrupt database.

I use it for #2 and #3 (and obviously used to do #1). I most recently used it on a live client system in October 2012 after a massive, multi-site SAN failure trashed databases and backups. I do not advise that you attempt #3 unless you’re confident you know what you’re doing and the side-effects on the Storage Engine from the byte(s) that you’re changing.

It is a very dangerous command because:

  1. It is an entirely physical change to the page – nothing is logged in the transaction log, and it cannot be rolled back.
  2. You can change any page in any database. For instance, you could use it to modify a page in master so that the instance immediately shuts down and will not start until master is rebuilt and restored.
  3. Anyone with sysadmin privileges can use it and there is no way to stop it.
  4. It breaks the support of your database.

You can very easily shoot yourself in the foot very badly playing around with this command. This isn’t hyperbole – it’s just the truth.

I’m going to do a series of posts showing the use of this command – as there are some cool things you can do with it for demo purposes.

(Toolbars to easily copy and paste code have been deliberately disabled in this post.)

The syntax is:

DBCC TRACEON (2588);
GO
DBCC HELP ('WRITEPAGE');
GO

 

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

The parameters mean:

  • ‘dbname’ | dbid : self-explanatory
  • fileid : file ID containing the page to change
  • pageid : zero-based page number within that file
  • offset : zero-based offset in bytes from the start of the page
  • length : number of bytes to change, from 1 to 8
  • data : the new data to insert (in hex, in the form ‘0xAABBCC’ – example three-byte string)
  • directORbufferpool : whether to bypass the buffer pool or not (0/1)

I’m actually a bit dismayed that someone added the final option to the syntax print-out, as it’s the most dangerous option – and the part I added in SQL Server 2005. If you specify ‘1’ for the directORbufferpool parameter, DBCC WRITEPAGE does the following:

  • Checkpoints the database and flushes all its pages out of the buffer pool
  • Unhooks SQL Server’s FCB (File Control Block) from the data file
  • Creates its own FCB for the file
  • Does a direct read of the page into DBCC’s memory
  • Modifies the page directly
  • Writes the page directly to disk, bypassing the buffer pool and any page protection generation (i.e. not recalculating the page checksum)
  • Fixes up the FCBs again

So the buffer pool knows nothing about the change to the page – it’s as if the I/O subsystem changed/corrupted the page. If you don’t specify that parameter, or specify ‘0’, the change occurs on the page in the buffer pool and the page checksum will be calculated correctly when the page is written to disk (albeit with other corruption issues maybe).

This means that you can force page checksum failures from within SQL Server – great for demos and testing. This is how I create corrupt databases.

To finish off this introductory post I’ll show you how to do just that, using a non-production server please…

CREATE DATABASE [CorruptDB];
GO
USE [CorruptDB];
GO

CREATE TABLE [Test] (
    [c1] INT IDENTITY,
	[c2] CHAR (8000) DEFAULT 'a');
GO

INSERT INTO [Test] DEFAULT VALUES;
GO

DBCC IND (N'CorruptDB', N'Test', -1);
GO

 

PageFID PagePID     IAMFID IAMPID      ObjectID    ...
------- ----------- ------ ----------- ----------- ...
1       154         NULL   NULL        101575400   ...
1       153         1      154         101575400   ...

I’m going to corrupt the data page – page (1:153):

ALTER DATABASE [CorruptDB] SET SINGLE_USER;
GO
DBCC WRITEPAGE (N'CorruptDB', 1, 153, 4000, 1, 0x45, 1);
GO

Now the database is corrupt and I’ve circumvented the buffer pool so the page checksum is incorrect now – watch…

SELECT * FROM [Test];
GO

 

c1          c2
----------- ---------- ...
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x41fb2e55; actual: 0x41fb4b55). It occurred during a read of page (1:153) in database ID 29 at offset 0x00000000132000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CorruptDB.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Nice eh? Yes, if that’s what you’re trying to do.

In the error log:

EXEC xp_readerrorlog;
GO

 

2013-02-05 14:32:56.760 spid55       Starting up database 'CorruptDB'.
2013-02-05 14:36:37.320 spid55       Setting database option SINGLE_USER to ON for database CorruptDB.
2013-02-05 14:36:37.320 spid55       User "APPLECROSS\paul" is modifying bytes 4000 to 4001 of page (1:153) in database "CorruptDB".
2013-02-05 14:37:55.160 spid55       Error: 824, Severity: 24, State: 2.
2013-02-05 14:37:55.160 spid55       SQL Server detected a logical consistency-based I/O error: blah blah blah

I’m not going to discuss how the use of DBCC WRITEPAGE is tracked within the database itself.

I’m going to blog a bunch more about DBCC WRITEPAGE over the next few months. If you’re going to play with it, please do not use it on a production system, and be very, very careful. It’s very easy to do something disastrous. And remember, if you use DBCC WRITEPAGE, you do so entirely at your own risk.

PS I really struggled with whether to post this or not, but in the end, given that it has and will continue to be posted by others, I want some authoritative information out there with warnings. There are all kinds of things you can do to destroy SQL Server data using Windows and the I/O subsystem (e.g. formatting an array) – doesn’t mean you go try it on a production system though without realizing the consequences. The same principle applies here. It isn’t irresponsible to post this information in the face of it becoming public anyway – better to have the complete story IMHO.

27 thoughts on “DBCC WRITEPAGE: an introduction

  1. Do you know why the length parameter is limited to values between one and eight?

    For manually creating corruption, this is plenty. However, if you’re trying to fix up records, rewrite slot pointers, etc, you’ll quickly have to write more than eight bytes. I assume you’ll be able to just loop through your data, but still, I’m wondering if there’s a more sinister reason for limiting it to just eight bytes.

  2. Interesting post – as I tend to stay away from this type of thing, it will actually help out in place of the hex editor method I have been using.

    If the last bit is 0, does it change the page and re-calculate the checksums so that we don’t get a checksum error but more of a logical inconsistency issue?

  3. Hi Paul-

    FYI every toolbar copy button worked for me, using Chrome on Windows.

    Regarding “It breaks the support of your database” does this mean WRITEPAGE marks the data files in similar fashion to updating the system objects, so that CSS can detect it?

      1. In the absence of giving people hints on how to bypass tracking, are there limits of “safe” use you could comment on?

        For example – if I want to induce corruption in a throwaway database on a production instance, am I safe to WRITEPAGE once and DROP DATABASE after? Do I need to induce the corruption on another server and restore to the production area so as not to “infect” other databases on the instance? Is use of this command so contagious that all collocated resources should be nuked from orbit after use lest you void your premier support agreement?

  4. I wanted to post why you would even build DBCC WRITEPAGE instead of having an external utility to write to the file directly, but although possible in theory, that’d be quite involved. You’d have to mount the database readonly, implement a DBCC PAGEOFFSET or suchlike to give you the offset within the file for a page, offline the database, twiddle the bits on disk, then online the database, and this doesn’t even represent a sudden I/O failure because you’ve brought the database offline in the meanwhile.

    You can implement a driver to do disk corruption for you (in fact I think I have seen people do this, at least something for file system corruption) but that still leaves you with the difficulty of simulating exactly the corruption you want if you want to test a particular case (and you don’t have anything to say about the buffer pool in this case, obviously).

    So overall, DBCC WRITEPAGE would be necessary. One thing that still bothers me, though: why isn’t DBCC WRITEPAGE disabled (or rather completely invisible) unless you twiddle an appropriate trace flag, preferably even a startup-only trace flag? I can see how that would make scenario #3 more difficult if you absolutely, positively must edit the database now and the server can’t be restarted (because it wouldn’t come up anymore, or something) but still, this is one of those things I wouldn’t guard by obscurity but just by putting ten feet of diamond-dust covered steel between it and the end users. And even if you know the trace flag you have to log in using the DAC, call an undocumented stored procedure called sys.ms_yes_i_want_dbcc_writepage_and_i_know_this_invalidates_support with a hash that depends on the machine’s MAC, the number of allocated pages in the database phase of the moon, and then you have to answer a round of trivia questions on 17th-century French poetry…

    OK, maybe I’m taking it too far. At least the database has to be in single user mode, that’s something.

    1. Yup – it was considered putting it under a trace flag and other protections, but they’re just further obfuscation methods. It’s necessary to have it available for #3 – sometimes CSS has to use it and shutting down a client production system to boot -m or set a startup trace flag isn’t palatable. At the time (2003-ish) it wasn’t seen as a problem – it likely won’t change now.

      1. Actually, I was thinking more about security — just because I’m a sysadmin on the SQL Server doesn’t mean I can alter the startup trace flags and restart the server, so that would mean WRITEPAGE isn’t available unless someone has physical access to the machine…

        …but, of course, this is mostly moot since any sysadmin can do SHUTDOWN anyway, and we probably *want* sysadmin to do anything SQL Server allows, which includes WRITEPAGE. From what I gather sysadmin should also be able to start SQL Server, but I wonder how this is supposed to work — AFAIK you need service control permissions, and those are part of Windows, not SQL Server. Does SQL Server twiddle the service ACL whenever the sysadmin role has a membership mutation? I’ll have to check in my copious free time.

        “CSS has to use it” could be solved with a challenge/response authentication and a timeout… but I get why this is going pretty far for a statement you’re not supposed to know about in the first place.

  5. Paul,

    Thanks for the post. Does this mean if I ever use this, let’s say just changing 1 letter in 1 record, that this DB is no longer going to be supported by MS for the life of the DB?

    If I upgrade to SQL 2020 after using DBCC WRITEPAGE in 2012, would Microsoft reserve the right to not support it anymore although the DB has gone through many upgrades and such? Would you have to basically create a brand new database and load up all the data before it is ‘fully supportable’ again?

    1. It depends what you change – but I’m not going to discuss the specifics of how it’s tracked.

      If you ever need to fix corruption that requires manual bit-twiddling, you really should export to a new database anyway.

  6. Paul,
    What you did writing about this requires a lot courage; because of the danger it could do to an organization’s database, but the responsibility that you feel as re-writer/author of the tool requires you to make sure others don’t presume to know what exactly this tool will do and persuade juniors dba’s to play with it and possible screw something up. In my humble opinion; you did what you supposed to do in a very professional way like everything else you write.
    Thanks to you and your wife for all the write you guys do out there and teach to the community.

  7. Thanks for this, I think I will leave DBCC WRITEPAGE alone.
    But DBCC TRACEON (2588); DBCC HELP (‘?’); is great.
    I might throw my old Sybase Administration book away now :-).

  8. Paul, thanks for the post! I have an awesome new interview question now :)

    There have been a few times when I’ve wanted to deliberately corrupt a test database and I appreciate having a SQL way to do it; I don’t have to mess with bit-twiddling utils anymore.

    Don’t feel guilty for creating the rope, you can’t control how it gets used. Just like TRUNCATE TABLE .. sort of. I’m unfortunately “seasoned enough” (nice way of saying geezer) to remember using Norton utils to hex-edit corrupted partition tables and worn-out floppy disk sectors, and I’m glad the utilities were available. If I killed my disk, I wasn’t going to blame Norton for it. I might kick myself if I didn’t have a backup, but that’s a whole other topic…

  9. Thank you for your interesting post abut DBCC WRITEPAGE, it’s very useful for demonstrating DBCC CHECKDB and corrupt databases. It used to be complicated to create a corrupt database, but with DBCC WRITEPAGE it’s very easy, maybe to easy. I have seen some nasty cases where the DBA:s have no clue of what to do in case of corruption and that’s scary.
    I can use DBCC WRITEPAGE to cause corruption for “disaster recovery” training and tests for students. Thanks for sharing.
    twitter: @h_winther

  10. Great post. I’ve been playing around with simulated corruption and finally got to really understand why my “corrupted” database wouldn’t wouldn’t pickup the page from the mirror system: unless directORbufferpool=1 the checksum is still calculated and there is no “corruption”.
    That recalculation has a funny effect: I did a “DBCC WRITEPAGE” to ‘update’ some data, and everything kept working fine and (I even got the “updated” row data doing a SELECT).
    After I shutdown the server and made some hex edit of file on the exact same page, when the SQL Server realized that the page was now corrupted and got it from mirror, it ‘lost’ the update I had done via “writepage” because it wasn’t replicate via any transaction log.

  11. IMO this should be considered a vulnerability where the risk far outweighs the benefit, and the back door should be closed. The company that doesn’t have a solid backup will learn the lesson the hard way but it seems to me this feature/defect should be removed in light of it’s publicity. Where is Microsoft on that possibility?

    1. Appreciate your thoughts. However, a vulnerability why? Only SA can run the command so it’s not like a random user can corrupt a database. And how is it different from being able to hex edit files or backups? It’s just easier. I don’t agree with the ‘learning the lesson the hard way’ theory if there’s a way to salvage data. From talking to the dev team and CSS, Microsoft has no plans to remove this in a future release.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.