The most dangerous command in SQL

Vijay is a SQL Server/Microsoft Data Platform professional with over 10 years of experience working in various fields such as financial, healthcare, and Manufacturing Domains. He has worked in various DB-related roles from database Modeler to database developer to Data Warehouse.

The most dangerous command in SQL

There’s one command, in particular, that has been effectively hidden since it was introduced pre-SQL Server 2000.

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

Well, no danger of death 

DBCC WRITEPAGE allows you to 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.

The purposes of DBCC WRITEPAGE are:

1. To allow automated testing of DBCC CHECKDBand 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.

It’s not advised 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 the 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.

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)

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

The syntax is:

12 3 4

DBCC TRACEON (2588);GO DBCC HELP (‘WRITEPAGE’); GO

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.