Protect Yourself from Using DBCC WritePage

Posted on by John HLeave a Comment on Protect Yourself from Using DBCC WritePage

I blogged DBCC WritePage a year ago here http://www.sqlnotes.info/2011/11/23/dbcc-writepage/. It’s an extremely dangerous command especially with the last parameter “directORbufferpool” of this command turned on. I also showed you how to fix the page checksum here http://www.sqlnotes.info/2013/05/02/fix-page-checksum/. To protect yourself, please ensure you are operating on the database that you want, not system databases. Taking a full backup before playing this command is important. Never run it in your production. Beside that, there is a simple way to rollback changes made by DBCC WritePage…

Continue reading “Protect Yourself from Using DBCC WritePage” →

Fix Page Checksum

Posted on by John H8 Comments on Fix Page Checksum

A checksum value is tagged with every data page in SQL Server. This is used to detect issues on the storage system to ensure what has been read from the disk is what has been written to the disk. When the checksum is detected which is not the one it’s supposed to be, the page will not be read into the buffer pool. SQL Server will return error. If you are sure the data on the page are all correct or the checksum is broken purposely, you do have the option to fix it even you don’t know how to calculate it.

Continue reading “Fix Page Checksum” →

SQL Server +0 and -0

Posted on by John H2 Comments on SQL Server +0 and -0

This is a very interesting error happened in one of my clients’ databases. Their database has been running for at least 12 years without a single problem. They run DBCC CheckDB regularly to ensure the database is in healthy state until few days ago their DBA ran DBCC CHECKTABLE with data_purity and get

Msg 2570, Level 16, State 3, Line 1
Page (1:118), slot 0 in object ID 245575913, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043432960 (type "In-row data"). Column "Value" value is out of range for data type "numeric". Update column to a legal value.

Finally, they found this article, , http://support.microsoft.com/kb/923247. — Many things can cause this kind of error. In this blog post, I am going to demo how I repeat and fix this error — for fun.

Continue reading “SQL Server +0 and -0” →

DBCC Config

Posted on by John H1 Comment on DBCC Config

Dbcc Config is an undocumented DBCC command which returns information of server configuration, which presented in DMV sys.configurations, on file 1, page 10 in master database. This command will return nothing if you don’t have trace flag 3604 on.

dbcc traceon(3604)
dbcc config --with tableresults
dbcc page(master,1,10, 3) -- with tableresults

Continue reading “DBCC Config” →

Freeze and Unfreeze Write IO on a Database

Posted on by John H5 Comments on Freeze and Unfreeze Write IO on a Database

Do you know you can suspend the write IO on one of your database and resume them later? 2 undocumented DBCC command allow you to do so, DBCC Freeze_IO and DBCC Thaw_IO. When DBCC Freeze_IO is called all the IO performing writing operation will be suspended immediately (not terminated). Once the IO is frozen, you can treat the database as a read-only database. Any writing activity will not return like you write data to a readonly database, they hang until you unfreeze the io by calling DBCC Thaw_IO.

Continue reading “Freeze and Unfreeze Write IO on a Database” →

DBCC WritePage

Posted on by John H23 Comments on DBCC WritePage

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…

Continue reading “DBCC WritePage” →