There isn’t really a need to do what I am doing in this blog post but I guess one reason for this is if you want to manufacture some sort of “targeted” corruption for a demo – here I will be making a value that is out of range for a data type. Or you just maybe interested in how to issue some of the commands.
First run DBCC IND (safe). So here I am interested in page 1120.
DBCC IND (N'AdventureWorks2012DR',N'Purchasing.PurchaseOrderDetail', -1) GO
Using DBCC Page(safe) for a page of interest with option 3 = page header plus detailed per-row interpretation
DBCC TRACEON (3604) DBCC PAGE (N'AdventureWorks2012DR',1,1120,3);
Snippet:
So I decided to “break” column 3 on slot 1 = DueDate using DBCC WRITEPAGE (Absolutely dangerous)
Below I have Highlighted parts (orange) that I use to calculate the offset in decimal – this being column offset + the offset of the record on page.
KeyHashValue = (e2338e2f4a9f)
Slot 1 Offset 0x98 Length 56
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 56
Memory Dump @0x499EC098
00000000: 10003400 02000000 02000000 00000000 †..4………….
00000010: 65960000 03006701 000080e2 06000000 †e–….g….â….
00000020: 0000012c 01000001 00000000 00000000 †…,…………
00000030: 5e960000 09000000 †††††††††††††††††††^–.. …
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
PurchaseOrderID = 2
Slot 1 Column 2 Offset 0x8 Length 4 Length (physical) 4
PurchaseOrderDetailID = 2
Slot 1 Column 3 Offset 0xc Length 8 Length (physical) 8
DueDate = 2005-05-31 00:00:00.000
Slot 1 Column 4 Offset 0x14 Length 2 Length (physical) 2
OrderQty = 3
Slot 1 Column 5 Offset 0x16 Length 4 Length (physical) 4
ProductID = 359
Slot 1 Column 6 Offset 0x1a Length 8 Length (physical) 8
UnitPrice = $45.1200
Slot 1 Column 8 Offset 0x22 Length 5 Length (physical) 5
ReceivedQty = 3.00
Convert to decimal the offset of RECORD ON PAGE + column offset C = 12(hex to decimal)
SELECT CONVERT (INT, 0x98 + 12) as [offset decimal] = 164
On our page 1120 at the calculated offset above I am going to affect 8 bytes.
DO NOT USE IN PRODUCTION – THIS IS A WARNING…
--Length 8 bytes DBCC WRITEPAGE (N'AdventureWorks2012DR', 1, 1120, 164, 8, 0Xfffffffffffffc11) GO -- Check DBCC CHECKDB (N'AdventureWorks2012DR') WITH NO_INFOMSGS
Whoops I did it again.
Msg 2570, Level 16, State 3, Line 72
Page (1:1120), slot 1 in object ID 594101157, index ID 1, partition ID 72057594046578688, alloc unit ID 72057594049134592 (type “In-row data”). Column “DueDate” value is out of range for data type “datetime”. Update column to a legal value.
LOOKING AT DBCC PAGE:
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
PurchaseOrderID = 2
Slot 1 Column 2 Offset 0x8 Length 4 Length (physical) 4
PurchaseOrderDetailID = 2
Slot 1 Column 3 Offset 0xc Length 8 Length (physical) 8
DueDate = INVALID COLUMN VALUE
LETS FIX THIS UP:
Let’s put in a value that makes sense. You can start piecing things together to build an update statement. The error message from CHECKDB says object id =594101157 hence run
select OBJECT_NAME(594101157) AS [Table] = PurchaseOrderDetail.
For the rest we know from DBCC PAGE the column to update(illegal value) and for which PurchaseOrderID (slot 1 column 1)
UPDATE [Purchasing].[PurchaseOrderDetail] SET DueDate = GETDATE() WHERE PurchaseOrderDetailID = 2
After the update:
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
PurchaseOrderID = 2
Slot 1 Column 2 Offset 0x8 Length 4 Length (physical) 4
PurchaseOrderDetailID = 2
Slot 1 Column 3 Offset 0xc Length 8 Length (physical) 8
DueDate = 2016-08-02 14:34:20.387
DBCC CHECKDB (N'AdventureWorks2012DR') WITH NO_INFOMSGS - Command(s) completed successfully.
My fav. article
LikeLike