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” →

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” →

Truncate

Posted on by John H1 Comment on Truncate

What are the differences between TRUNCATE and DELETE? You may immediately point out that:

  1. Truncates reset the identity value
  2. Truncates can’t have where clause
  3. Truncates can’t be run on a table which is referenced by other tables
  4. Truncates do not fire triggers.
  5. Truncates can’t be run on a table which has depending objects such as indexed views.
  6. Truncates can’t be run on a table which has published by merge or transactional replication publication
  7. Truncates need db_owner and db_ddladmin permission.
  8. Truncates need locks on the table and schema but do not need locks on rows of the tables

Continue reading “Truncate” →

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” →

Page Type

Posted on by John H4 Comments on Page Type

There are different types of pages in SQL Server. No matter what types of pages are, the layout of the page is the same. A data file consists of numbers of 8k-pages. A Page includes 8192 bytes. First 96 bytes are used for header. The rest of the space is for data. A variable length row offset array (or slot array) is located at the end of every page and grows backwards. Count of records (size of array) is saved in the header. The size of each element in the offset array is 2 bytes. Records in a page are not sorted even though it is an index page. If the data needs to be sorted, the offset array will be is sorted by the key of the index.

As far as I know, there are about 14 types of pages in SQL Server data file.

  • Type 1 – Data page.
    • Data records in heap
    • Clustered index leaf-level
    • Location can be random
  • Type 2 – Index page
    • Non-clustered index
    • Non-leave-level clustered index
    • Location can be random

    • Continue reading “Page Type” →