Well, with the name “blobeater” it was about time I did another post on CHECKDB.

This time I want to talk about another strategy that you could use to manage CHECKDB on your TB sized databases – something that I actually do.

My bigger databases use partitioning that are stored on separate filegroups.

Let’s assume we have – Primary (obviously), AWS and LAM. I make use of the fact that my database has multiple filegroups so I then use DBCC CHECKFILEGROUP. This checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the database as stated by Books on Line.

So I issue:

USE Mydb
GO
-- Primary day 1
DBCC CHECKFILEGROUP;
GO
DBCC CHECKCATALOG

--other FileGroups Day2
DBCC CHECKFILEGROUP ('AWS')
GO
DBCC CHECKCATALOG

--other FileGroups Day3
DBCC CHECKFILEGROUP ('LAM')
GO
DBCC CHECKCATALOG

You may be wondering why I am issuing the CHECKCATALOG separately. Well this is because only CHECKALLOC and CHECKTABLE are done via CHECKFILEGROUP so you really should be doing CHECKCATALOG independently too. This approach gives you the ability to spread the burden over a couple of days rather than all in 1 day / window.

Just be aware of this bug http://www.sqlskills.com/blogs/paul/dbcc-checkfilegroup-bug-on-sql-server-2008/

If you do not have filegroups you could spread CHECKALLOC, CHECKCATALOG and CHECKTABLE over X days – I have some code that does this – https://blobeater.wordpress.com/category/sql-server-2/development/

I was going through some online courses and came across Erin Stellato’s Course on DBCC commands.http://www.sqlskills.com/blogs/paul/new-course-understanding-and-using-dbcc-commands/

I learnt something new! It’s not that complicated but I have found it useful. It is called DBCC HELP.

The command returns syntax information for a specific DBCC command, pretty useful if you have forgotten the syntax to write for example CHECKDB with extended logical checks and no informational messages. (Just an example)

So execute the below.

DBCC HELP ('?');
GO

This will give you a list of what you can pass into DBCC HELP – Yep checkdb is there.

checkalloc
checkcatalog
checkconstraints
checkdb
checkfilegroup
checkident
checktable
cleantable
dbreindex
dropcleanbuffers
free
freeproccache
freesystemcache
help
indexdefrag
inputbuffer
opentran
outputbuffer
pintable
proccache
show_statistics
showcontig
shrinkdatabase
shrinkfile
sqlperf
traceoff
traceon
tracestatus
unpintable
updateusage
useroptions

So let’s pass in CHECKDB.

DBCC HELP (CHECKDB);
GO

OUTPUT:

/**
dbcc CHECKDB
(
    { 'database_name' | database_id | 0 }
    [ , NOINDEX
    | { REPAIR_ALLOW_DATA_LOSS
    | REPAIR_FAST
    | REPAIR_REBUILD
    } ]
)
    [ WITH
        {
            [ ALL_ERRORMSGS ]
            [ , [ NO_INFOMSGS ] ]
            [ , [ TABLOCK ] ]
            [ , [ ESTIMATEONLY ] ]
            [ , [ PHYSICAL_ONLY ] ]
            [ , [ DATA_PURITY ] ]
            [ , [ EXTENDED_LOGICAL_CHECKS  ] ]
        }
    ]

**/

So now you will know without googling how to write your command:

DBCC CHECKDB ('AdventureWorks2020')
WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS

I am in a fix-it mood so for this blog I am going to corrupt a page and then show you how to recover it using a page restore.

So let’s begin.

This is a very basic setup just to highlight the steps involved.

CREATE DATABASE [fixit];
GO
USE [fixit]
GO
ALTER DATABASE [fixit] SET RECOVERY FULL
GO
CREATE TABLE [xbox] (
[c1] INT IDENTITY,
[c2] CHAR (8000) DEFAULT 'a');
GO

INSERT INTO [xbox] DEFAULT VALUES;
GO

Let’s do some backups

USE master
GO
BACKUP DATABASE [fixit] TO DISK = 'C:\sqlserver\fixitfull11.bak'
GO
BACKUP LOG [fixit] TO DISK = 'C:\sqlserver\fixitLOG11.bak'

Let’s look at DBCC IND to get some pageIDs

DBCC IND (N'fixit', N'xbox', -1);
GO

Trash it

I am going to trash data page 78 (type 1) using DBCC WRITEPAGE – This is a pretty dangerous command – DO NOT USE IT IN PRODUCTION. Actually, don’t use it all if you are not comfortable with it! This is going to be executed on my laptop, my hardware using my software – so I accept any consequence…..

 

ALTER DATABASE fixit SET SINGLE_USER;
GO
DBCC WRITEPAGE (N'fixit', 1, 78, 4000, 1, 0x45, 1);
GO
ALTER DATABASE fixit SET MULTI_USER;
GO

SELECT * FROM [fixit].[dbo].[xbox]

Msg 824, Level 24, State 2, Line 37
SQL Server detected a logical consistency-based I/O error:
It occurred during a read of page (1:78) in database ID 22 at offset 0x0000000009c000

DBCC CHECKDB ('Naughty') WITH NO_INFOMSGS, ALL_ERRORMSGS

CHECKDB found 0 allocation errors and 2 consistency errors in table ‘xbox’ (object ID 2105058535).
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘fixit’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (fixit).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

As a side note this will get recorded into the suspect_pages table within msdb.

SELECT * FROM [msdb].[dbo].[suspect_pages]

Some more general activity.

USE [fixit]
go

INSERT INTO [dbo].[xbox]
([c2])
VALUES
('b')
GO

We want the data back!

Recovery time
First step is to issue a tail-log backup.

USE master
GO
-- tail
BACKUP log [fixit] TO DISK = 'C:\sqlserver\fixitLOG12.bak'

--So now we start the page recovery
RESTORE DATABASE [fixit]
PAGE = '1:78'
FROM DISK = 'C:\sqlserver\fixitfull11.bak'
WITH NORECOVERY
GO

RESTORE LOG [fixit] FROM
DISK = 'C:\sqlserver\fixitLOG11.bak'
WITH NORECOVERY
GO

RESTORE LOG [fixit] FROM
DISK = 'C:\sqlserver\fixitLOG12.bak'
WITH NORECOVERY
GO

-- Finally bring it back
RESTORE DATABASE [fixit] WITH RECOVERY

DBCC CHECKDB('Fixit')

CHECKDB found 0 allocation errors and 0 consistency errors in database ‘fixit’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

USE [fixit]
GO
SELECT * FROM [dbo].[xbox]

Just be aware of certain limitations such as that allocation pages Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and Page Free Space (PFS) pages cannot be recovered. More information can be found at https://msdn.microsoft.com/en-us/library/ms175168.aspx.

Happy fixing!

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.