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